1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56TABLE_PARTS = ("this", "db", "catalog")
  57
  58
  59class Expression(metaclass=_Expression):
  60    """
  61    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  62    context, such as its child expressions, their names (arg keys), and whether a given child expression
  63    is optional or not.
  64
  65    Attributes:
  66        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  67            and representing expressions as strings.
  68        arg_types: determines what arguments (child nodes) are supported by an expression. It
  69            maps arg keys to booleans that indicate whether the corresponding args are optional.
  70        parent: a reference to the parent expression (or None, in case of root expressions).
  71        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  72            uses to refer to it.
  73        comments: a list of comments that are associated with a given expression. This is used in
  74            order to preserve comments when transpiling SQL code.
  75        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  76            optimizer, in order to enable some transformations that require type information.
  77        meta: a dictionary that can be used to store useful metadata for a given expression.
  78
  79    Example:
  80        >>> class Foo(Expression):
  81        ...     arg_types = {"this": True, "expression": False}
  82
  83        The above definition informs us that Foo is an Expression that requires an argument called
  84        "this" and may also optionally receive an argument called "expression".
  85
  86    Args:
  87        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  88    """
  89
  90    key = "expression"
  91    arg_types = {"this": True}
  92    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  93
  94    def __init__(self, **args: t.Any):
  95        self.args: t.Dict[str, t.Any] = args
  96        self.parent: t.Optional[Expression] = None
  97        self.arg_key: t.Optional[str] = None
  98        self.comments: t.Optional[t.List[str]] = None
  99        self._type: t.Optional[DataType] = None
 100        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 101        self._hash: t.Optional[int] = None
 102
 103        for arg_key, value in self.args.items():
 104            self._set_parent(arg_key, value)
 105
 106    def __eq__(self, other) -> bool:
 107        return type(self) is type(other) and hash(self) == hash(other)
 108
 109    @property
 110    def hashable_args(self) -> t.Any:
 111        return frozenset(
 112            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 113            for k, v in self.args.items()
 114            if not (v is None or v is False or (type(v) is list and not v))
 115        )
 116
 117    def __hash__(self) -> int:
 118        if self._hash is not None:
 119            return self._hash
 120
 121        return hash((self.__class__, self.hashable_args))
 122
 123    @property
 124    def this(self) -> t.Any:
 125        """
 126        Retrieves the argument with key "this".
 127        """
 128        return self.args.get("this")
 129
 130    @property
 131    def expression(self) -> t.Any:
 132        """
 133        Retrieves the argument with key "expression".
 134        """
 135        return self.args.get("expression")
 136
 137    @property
 138    def expressions(self) -> t.List[t.Any]:
 139        """
 140        Retrieves the argument with key "expressions".
 141        """
 142        return self.args.get("expressions") or []
 143
 144    def text(self, key) -> str:
 145        """
 146        Returns a textual representation of the argument corresponding to "key". This can only be used
 147        for args that are strings or leaf Expression instances, such as identifiers and literals.
 148        """
 149        field = self.args.get(key)
 150        if isinstance(field, str):
 151            return field
 152        if isinstance(field, (Identifier, Literal, Var)):
 153            return field.this
 154        if isinstance(field, (Star, Null)):
 155            return field.name
 156        return ""
 157
 158    @property
 159    def is_string(self) -> bool:
 160        """
 161        Checks whether a Literal expression is a string.
 162        """
 163        return isinstance(self, Literal) and self.args["is_string"]
 164
 165    @property
 166    def is_number(self) -> bool:
 167        """
 168        Checks whether a Literal expression is a number.
 169        """
 170        return isinstance(self, Literal) and not self.args["is_string"]
 171
 172    @property
 173    def is_int(self) -> bool:
 174        """
 175        Checks whether a Literal expression is an integer.
 176        """
 177        if self.is_number:
 178            try:
 179                int(self.name)
 180                return True
 181            except ValueError:
 182                pass
 183        return False
 184
 185    @property
 186    def is_star(self) -> bool:
 187        """Checks whether an expression is a star."""
 188        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 189
 190    @property
 191    def alias(self) -> str:
 192        """
 193        Returns the alias of the expression, or an empty string if it's not aliased.
 194        """
 195        if isinstance(self.args.get("alias"), TableAlias):
 196            return self.args["alias"].name
 197        return self.text("alias")
 198
 199    @property
 200    def alias_column_names(self) -> t.List[str]:
 201        table_alias = self.args.get("alias")
 202        if not table_alias:
 203            return []
 204        return [c.name for c in table_alias.args.get("columns") or []]
 205
 206    @property
 207    def name(self) -> str:
 208        return self.text("this")
 209
 210    @property
 211    def alias_or_name(self) -> str:
 212        return self.alias or self.name
 213
 214    @property
 215    def output_name(self) -> str:
 216        """
 217        Name of the output column if this expression is a selection.
 218
 219        If the Expression has no output name, an empty string is returned.
 220
 221        Example:
 222            >>> from sqlglot import parse_one
 223            >>> parse_one("SELECT a").expressions[0].output_name
 224            'a'
 225            >>> parse_one("SELECT b AS c").expressions[0].output_name
 226            'c'
 227            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 228            ''
 229        """
 230        return ""
 231
 232    @property
 233    def type(self) -> t.Optional[DataType]:
 234        return self._type
 235
 236    @type.setter
 237    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 238        if dtype and not isinstance(dtype, DataType):
 239            dtype = DataType.build(dtype)
 240        self._type = dtype  # type: ignore
 241
 242    def is_type(self, *dtypes) -> bool:
 243        return self.type is not None and self.type.is_type(*dtypes)
 244
 245    @property
 246    def meta(self) -> t.Dict[str, t.Any]:
 247        if self._meta is None:
 248            self._meta = {}
 249        return self._meta
 250
 251    def __deepcopy__(self, memo):
 252        copy = self.__class__(**deepcopy(self.args))
 253        if self.comments is not None:
 254            copy.comments = deepcopy(self.comments)
 255
 256        if self._type is not None:
 257            copy._type = self._type.copy()
 258
 259        if self._meta is not None:
 260            copy._meta = deepcopy(self._meta)
 261
 262        return copy
 263
 264    def copy(self):
 265        """
 266        Returns a deep copy of the expression.
 267        """
 268        new = deepcopy(self)
 269        new.parent = self.parent
 270        return new
 271
 272    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 273        if self.comments is None:
 274            self.comments = []
 275        if comments:
 276            for comment in comments:
 277                _, *meta = comment.split(SQLGLOT_META)
 278                if meta:
 279                    for kv in "".join(meta).split(","):
 280                        k, *v = kv.split("=")
 281                        value = v[0].strip() if v else True
 282                        self.meta[k.strip()] = value
 283                self.comments.append(comment)
 284
 285    def append(self, arg_key: str, value: t.Any) -> None:
 286        """
 287        Appends value to arg_key if it's a list or sets it as a new list.
 288
 289        Args:
 290            arg_key (str): name of the list expression arg
 291            value (Any): value to append to the list
 292        """
 293        if not isinstance(self.args.get(arg_key), list):
 294            self.args[arg_key] = []
 295        self.args[arg_key].append(value)
 296        self._set_parent(arg_key, value)
 297
 298    def set(self, arg_key: str, value: t.Any) -> None:
 299        """
 300        Sets arg_key to value.
 301
 302        Args:
 303            arg_key: name of the expression arg.
 304            value: value to set the arg to.
 305        """
 306        if value is None:
 307            self.args.pop(arg_key, None)
 308            return
 309
 310        self.args[arg_key] = value
 311        self._set_parent(arg_key, value)
 312
 313    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 314        if hasattr(value, "parent"):
 315            value.parent = self
 316            value.arg_key = arg_key
 317        elif type(value) is list:
 318            for v in value:
 319                if hasattr(v, "parent"):
 320                    v.parent = self
 321                    v.arg_key = arg_key
 322
 323    @property
 324    def depth(self) -> int:
 325        """
 326        Returns the depth of this tree.
 327        """
 328        if self.parent:
 329            return self.parent.depth + 1
 330        return 0
 331
 332    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 333        """Yields the key and expression for all arguments, exploding list args."""
 334        for k, vs in self.args.items():
 335            if type(vs) is list:
 336                for v in vs:
 337                    if hasattr(v, "parent"):
 338                        yield k, v
 339            else:
 340                if hasattr(vs, "parent"):
 341                    yield k, vs
 342
 343    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 344        """
 345        Returns the first node in this tree which matches at least one of
 346        the specified types.
 347
 348        Args:
 349            expression_types: the expression type(s) to match.
 350            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 351
 352        Returns:
 353            The node which matches the criteria or None if no such node was found.
 354        """
 355        return next(self.find_all(*expression_types, bfs=bfs), None)
 356
 357    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 358        """
 359        Returns a generator object which visits all nodes in this tree and only
 360        yields those that match at least one of the specified expression types.
 361
 362        Args:
 363            expression_types: the expression type(s) to match.
 364            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 365
 366        Returns:
 367            The generator object.
 368        """
 369        for expression, *_ in self.walk(bfs=bfs):
 370            if isinstance(expression, expression_types):
 371                yield expression
 372
 373    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 374        """
 375        Returns a nearest parent matching expression_types.
 376
 377        Args:
 378            expression_types: the expression type(s) to match.
 379
 380        Returns:
 381            The parent node.
 382        """
 383        ancestor = self.parent
 384        while ancestor and not isinstance(ancestor, expression_types):
 385            ancestor = ancestor.parent
 386        return t.cast(E, ancestor)
 387
 388    @property
 389    def parent_select(self) -> t.Optional[Select]:
 390        """
 391        Returns the parent select statement.
 392        """
 393        return self.find_ancestor(Select)
 394
 395    @property
 396    def same_parent(self) -> bool:
 397        """Returns if the parent is the same class as itself."""
 398        return type(self.parent) is self.__class__
 399
 400    def root(self) -> Expression:
 401        """
 402        Returns the root expression of this tree.
 403        """
 404        expression = self
 405        while expression.parent:
 406            expression = expression.parent
 407        return expression
 408
 409    def walk(self, bfs=True, prune=None):
 410        """
 411        Returns a generator object which visits all nodes in this tree.
 412
 413        Args:
 414            bfs (bool): if set to True the BFS traversal order will be applied,
 415                otherwise the DFS traversal will be used instead.
 416            prune ((node, parent, arg_key) -> bool): callable that returns True if
 417                the generator should stop traversing this branch of the tree.
 418
 419        Returns:
 420            the generator object.
 421        """
 422        if bfs:
 423            yield from self.bfs(prune=prune)
 424        else:
 425            yield from self.dfs(prune=prune)
 426
 427    def dfs(self, parent=None, key=None, prune=None):
 428        """
 429        Returns a generator object which visits all nodes in this tree in
 430        the DFS (Depth-first) order.
 431
 432        Returns:
 433            The generator object.
 434        """
 435        parent = parent or self.parent
 436        yield self, parent, key
 437        if prune and prune(self, parent, key):
 438            return
 439
 440        for k, v in self.iter_expressions():
 441            yield from v.dfs(self, k, prune)
 442
 443    def bfs(self, prune=None):
 444        """
 445        Returns a generator object which visits all nodes in this tree in
 446        the BFS (Breadth-first) order.
 447
 448        Returns:
 449            The generator object.
 450        """
 451        queue = deque([(self, self.parent, None)])
 452
 453        while queue:
 454            item, parent, key = queue.popleft()
 455
 456            yield item, parent, key
 457            if prune and prune(item, parent, key):
 458                continue
 459
 460            for k, v in item.iter_expressions():
 461                queue.append((v, item, k))
 462
 463    def unnest(self):
 464        """
 465        Returns the first non parenthesis child or self.
 466        """
 467        expression = self
 468        while type(expression) is Paren:
 469            expression = expression.this
 470        return expression
 471
 472    def unalias(self):
 473        """
 474        Returns the inner expression if this is an Alias.
 475        """
 476        if isinstance(self, Alias):
 477            return self.this
 478        return self
 479
 480    def unnest_operands(self):
 481        """
 482        Returns unnested operands as a tuple.
 483        """
 484        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 485
 486    def flatten(self, unnest=True):
 487        """
 488        Returns a generator which yields child nodes who's parents are the same class.
 489
 490        A AND B AND C -> [A, B, C]
 491        """
 492        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 493            if not type(node) is self.__class__:
 494                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 495
 496    def __str__(self) -> str:
 497        return self.sql()
 498
 499    def __repr__(self) -> str:
 500        return self._to_s()
 501
 502    def sql(self, dialect: DialectType = None, **opts) -> str:
 503        """
 504        Returns SQL string representation of this tree.
 505
 506        Args:
 507            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 508            opts: other `sqlglot.generator.Generator` options.
 509
 510        Returns:
 511            The SQL string.
 512        """
 513        from sqlglot.dialects import Dialect
 514
 515        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 516
 517    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 518        indent = "" if not level else "\n"
 519        indent += "".join(["  "] * level)
 520        left = f"({self.key.upper()} "
 521
 522        args: t.Dict[str, t.Any] = {
 523            k: ", ".join(
 524                v._to_s(hide_missing=hide_missing, level=level + 1)
 525                if hasattr(v, "_to_s")
 526                else str(v)
 527                for v in ensure_list(vs)
 528                if v is not None
 529            )
 530            for k, vs in self.args.items()
 531        }
 532        args["comments"] = self.comments
 533        args["type"] = self.type
 534        args = {k: v for k, v in args.items() if v or not hide_missing}
 535
 536        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 537        right += ")"
 538
 539        return indent + left + right
 540
 541    def transform(self, fun, *args, copy=True, **kwargs):
 542        """
 543        Recursively visits all tree nodes (excluding already transformed ones)
 544        and applies the given transformation function to each node.
 545
 546        Args:
 547            fun (function): a function which takes a node as an argument and returns a
 548                new transformed node or the same node without modifications. If the function
 549                returns None, then the corresponding node will be removed from the syntax tree.
 550            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 551                modified in place.
 552
 553        Returns:
 554            The transformed tree.
 555        """
 556        node = self.copy() if copy else self
 557        new_node = fun(node, *args, **kwargs)
 558
 559        if new_node is None or not isinstance(new_node, Expression):
 560            return new_node
 561        if new_node is not node:
 562            new_node.parent = node.parent
 563            return new_node
 564
 565        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 566        return new_node
 567
 568    @t.overload
 569    def replace(self, expression: E) -> E:
 570        ...
 571
 572    @t.overload
 573    def replace(self, expression: None) -> None:
 574        ...
 575
 576    def replace(self, expression):
 577        """
 578        Swap out this expression with a new expression.
 579
 580        For example::
 581
 582            >>> tree = Select().select("x").from_("tbl")
 583            >>> tree.find(Column).replace(Column(this="y"))
 584            (COLUMN this: y)
 585            >>> tree.sql()
 586            'SELECT y FROM tbl'
 587
 588        Args:
 589            expression: new node
 590
 591        Returns:
 592            The new expression or expressions.
 593        """
 594        if not self.parent:
 595            return expression
 596
 597        parent = self.parent
 598        self.parent = None
 599
 600        replace_children(parent, lambda child: expression if child is self else child)
 601        return expression
 602
 603    def pop(self: E) -> E:
 604        """
 605        Remove this expression from its AST.
 606
 607        Returns:
 608            The popped expression.
 609        """
 610        self.replace(None)
 611        return self
 612
 613    def assert_is(self, type_: t.Type[E]) -> E:
 614        """
 615        Assert that this `Expression` is an instance of `type_`.
 616
 617        If it is NOT an instance of `type_`, this raises an assertion error.
 618        Otherwise, this returns this expression.
 619
 620        Examples:
 621            This is useful for type security in chained expressions:
 622
 623            >>> import sqlglot
 624            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 625            'SELECT x, z FROM y'
 626        """
 627        assert isinstance(self, type_)
 628        return self
 629
 630    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 631        """
 632        Checks if this expression is valid (e.g. all mandatory args are set).
 633
 634        Args:
 635            args: a sequence of values that were used to instantiate a Func expression. This is used
 636                to check that the provided arguments don't exceed the function argument limit.
 637
 638        Returns:
 639            A list of error messages for all possible errors that were found.
 640        """
 641        errors: t.List[str] = []
 642
 643        for k in self.args:
 644            if k not in self.arg_types:
 645                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 646        for k, mandatory in self.arg_types.items():
 647            v = self.args.get(k)
 648            if mandatory and (v is None or (isinstance(v, list) and not v)):
 649                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 650
 651        if (
 652            args
 653            and isinstance(self, Func)
 654            and len(args) > len(self.arg_types)
 655            and not self.is_var_len_args
 656        ):
 657            errors.append(
 658                f"The number of provided arguments ({len(args)}) is greater than "
 659                f"the maximum number of supported arguments ({len(self.arg_types)})"
 660            )
 661
 662        return errors
 663
 664    def dump(self):
 665        """
 666        Dump this Expression to a JSON-serializable dict.
 667        """
 668        from sqlglot.serde import dump
 669
 670        return dump(self)
 671
 672    @classmethod
 673    def load(cls, obj):
 674        """
 675        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 676        """
 677        from sqlglot.serde import load
 678
 679        return load(obj)
 680
 681    def and_(
 682        self,
 683        *expressions: t.Optional[ExpOrStr],
 684        dialect: DialectType = None,
 685        copy: bool = True,
 686        **opts,
 687    ) -> Condition:
 688        """
 689        AND this condition with one or multiple expressions.
 690
 691        Example:
 692            >>> condition("x=1").and_("y=1").sql()
 693            'x = 1 AND y = 1'
 694
 695        Args:
 696            *expressions: the SQL code strings to parse.
 697                If an `Expression` instance is passed, it will be used as-is.
 698            dialect: the dialect used to parse the input expression.
 699            copy: whether or not to copy the involved expressions (only applies to Expressions).
 700            opts: other options to use to parse the input expressions.
 701
 702        Returns:
 703            The new And condition.
 704        """
 705        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 706
 707    def or_(
 708        self,
 709        *expressions: t.Optional[ExpOrStr],
 710        dialect: DialectType = None,
 711        copy: bool = True,
 712        **opts,
 713    ) -> Condition:
 714        """
 715        OR this condition with one or multiple expressions.
 716
 717        Example:
 718            >>> condition("x=1").or_("y=1").sql()
 719            'x = 1 OR y = 1'
 720
 721        Args:
 722            *expressions: the SQL code strings to parse.
 723                If an `Expression` instance is passed, it will be used as-is.
 724            dialect: the dialect used to parse the input expression.
 725            copy: whether or not to copy the involved expressions (only applies to Expressions).
 726            opts: other options to use to parse the input expressions.
 727
 728        Returns:
 729            The new Or condition.
 730        """
 731        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 732
 733    def not_(self, copy: bool = True):
 734        """
 735        Wrap this condition with NOT.
 736
 737        Example:
 738            >>> condition("x=1").not_().sql()
 739            'NOT x = 1'
 740
 741        Args:
 742            copy: whether or not to copy this object.
 743
 744        Returns:
 745            The new Not instance.
 746        """
 747        return not_(self, copy=copy)
 748
 749    def as_(
 750        self,
 751        alias: str | Identifier,
 752        quoted: t.Optional[bool] = None,
 753        dialect: DialectType = None,
 754        copy: bool = True,
 755        **opts,
 756    ) -> Alias:
 757        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 758
 759    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 760        this = self.copy()
 761        other = convert(other, copy=True)
 762        if not isinstance(this, klass) and not isinstance(other, klass):
 763            this = _wrap(this, Binary)
 764            other = _wrap(other, Binary)
 765        if reverse:
 766            return klass(this=other, expression=this)
 767        return klass(this=this, expression=other)
 768
 769    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 770        return Bracket(
 771            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 772        )
 773
 774    def __iter__(self) -> t.Iterator:
 775        if "expressions" in self.arg_types:
 776            return iter(self.args.get("expressions") or [])
 777        # We define this because __getitem__ converts Expression into an iterable, which is
 778        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 779        # See: https://peps.python.org/pep-0234/
 780        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 781
 782    def isin(
 783        self,
 784        *expressions: t.Any,
 785        query: t.Optional[ExpOrStr] = None,
 786        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 787        copy: bool = True,
 788        **opts,
 789    ) -> In:
 790        return In(
 791            this=maybe_copy(self, copy),
 792            expressions=[convert(e, copy=copy) for e in expressions],
 793            query=maybe_parse(query, copy=copy, **opts) if query else None,
 794            unnest=Unnest(
 795                expressions=[
 796                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 797                ]
 798            )
 799            if unnest
 800            else None,
 801        )
 802
 803    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 804        return Between(
 805            this=maybe_copy(self, copy),
 806            low=convert(low, copy=copy, **opts),
 807            high=convert(high, copy=copy, **opts),
 808        )
 809
 810    def is_(self, other: ExpOrStr) -> Is:
 811        return self._binop(Is, other)
 812
 813    def like(self, other: ExpOrStr) -> Like:
 814        return self._binop(Like, other)
 815
 816    def ilike(self, other: ExpOrStr) -> ILike:
 817        return self._binop(ILike, other)
 818
 819    def eq(self, other: t.Any) -> EQ:
 820        return self._binop(EQ, other)
 821
 822    def neq(self, other: t.Any) -> NEQ:
 823        return self._binop(NEQ, other)
 824
 825    def rlike(self, other: ExpOrStr) -> RegexpLike:
 826        return self._binop(RegexpLike, other)
 827
 828    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 829        div = self._binop(Div, other)
 830        div.args["typed"] = typed
 831        div.args["safe"] = safe
 832        return div
 833
 834    def __lt__(self, other: t.Any) -> LT:
 835        return self._binop(LT, other)
 836
 837    def __le__(self, other: t.Any) -> LTE:
 838        return self._binop(LTE, other)
 839
 840    def __gt__(self, other: t.Any) -> GT:
 841        return self._binop(GT, other)
 842
 843    def __ge__(self, other: t.Any) -> GTE:
 844        return self._binop(GTE, other)
 845
 846    def __add__(self, other: t.Any) -> Add:
 847        return self._binop(Add, other)
 848
 849    def __radd__(self, other: t.Any) -> Add:
 850        return self._binop(Add, other, reverse=True)
 851
 852    def __sub__(self, other: t.Any) -> Sub:
 853        return self._binop(Sub, other)
 854
 855    def __rsub__(self, other: t.Any) -> Sub:
 856        return self._binop(Sub, other, reverse=True)
 857
 858    def __mul__(self, other: t.Any) -> Mul:
 859        return self._binop(Mul, other)
 860
 861    def __rmul__(self, other: t.Any) -> Mul:
 862        return self._binop(Mul, other, reverse=True)
 863
 864    def __truediv__(self, other: t.Any) -> Div:
 865        return self._binop(Div, other)
 866
 867    def __rtruediv__(self, other: t.Any) -> Div:
 868        return self._binop(Div, other, reverse=True)
 869
 870    def __floordiv__(self, other: t.Any) -> IntDiv:
 871        return self._binop(IntDiv, other)
 872
 873    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 874        return self._binop(IntDiv, other, reverse=True)
 875
 876    def __mod__(self, other: t.Any) -> Mod:
 877        return self._binop(Mod, other)
 878
 879    def __rmod__(self, other: t.Any) -> Mod:
 880        return self._binop(Mod, other, reverse=True)
 881
 882    def __pow__(self, other: t.Any) -> Pow:
 883        return self._binop(Pow, other)
 884
 885    def __rpow__(self, other: t.Any) -> Pow:
 886        return self._binop(Pow, other, reverse=True)
 887
 888    def __and__(self, other: t.Any) -> And:
 889        return self._binop(And, other)
 890
 891    def __rand__(self, other: t.Any) -> And:
 892        return self._binop(And, other, reverse=True)
 893
 894    def __or__(self, other: t.Any) -> Or:
 895        return self._binop(Or, other)
 896
 897    def __ror__(self, other: t.Any) -> Or:
 898        return self._binop(Or, other, reverse=True)
 899
 900    def __neg__(self) -> Neg:
 901        return Neg(this=_wrap(self.copy(), Binary))
 902
 903    def __invert__(self) -> Not:
 904        return not_(self.copy())
 905
 906
 907IntoType = t.Union[
 908    str,
 909    t.Type[Expression],
 910    t.Collection[t.Union[str, t.Type[Expression]]],
 911]
 912ExpOrStr = t.Union[str, Expression]
 913
 914
 915class Condition(Expression):
 916    """Logical conditions like x AND y, or simply x"""
 917
 918
 919class Predicate(Condition):
 920    """Relationships like x = y, x > 1, x >= y."""
 921
 922
 923class DerivedTable(Expression):
 924    @property
 925    def selects(self) -> t.List[Expression]:
 926        return self.this.selects if isinstance(self.this, Subqueryable) else []
 927
 928    @property
 929    def named_selects(self) -> t.List[str]:
 930        return [select.output_name for select in self.selects]
 931
 932
 933class Unionable(Expression):
 934    def union(
 935        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 936    ) -> Unionable:
 937        """
 938        Builds a UNION expression.
 939
 940        Example:
 941            >>> import sqlglot
 942            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 943            'SELECT * FROM foo UNION SELECT * FROM bla'
 944
 945        Args:
 946            expression: the SQL code string.
 947                If an `Expression` instance is passed, it will be used as-is.
 948            distinct: set the DISTINCT flag if and only if this is true.
 949            dialect: the dialect used to parse the input expression.
 950            opts: other options to use to parse the input expressions.
 951
 952        Returns:
 953            The new Union expression.
 954        """
 955        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 956
 957    def intersect(
 958        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 959    ) -> Unionable:
 960        """
 961        Builds an INTERSECT expression.
 962
 963        Example:
 964            >>> import sqlglot
 965            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 966            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 967
 968        Args:
 969            expression: the SQL code string.
 970                If an `Expression` instance is passed, it will be used as-is.
 971            distinct: set the DISTINCT flag if and only if this is true.
 972            dialect: the dialect used to parse the input expression.
 973            opts: other options to use to parse the input expressions.
 974
 975        Returns:
 976            The new Intersect expression.
 977        """
 978        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 979
 980    def except_(
 981        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 982    ) -> Unionable:
 983        """
 984        Builds an EXCEPT expression.
 985
 986        Example:
 987            >>> import sqlglot
 988            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 989            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 990
 991        Args:
 992            expression: the SQL code string.
 993                If an `Expression` instance is passed, it will be used as-is.
 994            distinct: set the DISTINCT flag if and only if this is true.
 995            dialect: the dialect used to parse the input expression.
 996            opts: other options to use to parse the input expressions.
 997
 998        Returns:
 999            The new Except expression.
1000        """
1001        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1002
1003
1004class UDTF(DerivedTable, Unionable):
1005    @property
1006    def selects(self) -> t.List[Expression]:
1007        alias = self.args.get("alias")
1008        return alias.columns if alias else []
1009
1010
1011class Cache(Expression):
1012    arg_types = {
1013        "this": True,
1014        "lazy": False,
1015        "options": False,
1016        "expression": False,
1017    }
1018
1019
1020class Uncache(Expression):
1021    arg_types = {"this": True, "exists": False}
1022
1023
1024class Refresh(Expression):
1025    pass
1026
1027
1028class DDL(Expression):
1029    @property
1030    def ctes(self):
1031        with_ = self.args.get("with")
1032        if not with_:
1033            return []
1034        return with_.expressions
1035
1036    @property
1037    def named_selects(self) -> t.List[str]:
1038        if isinstance(self.expression, Subqueryable):
1039            return self.expression.named_selects
1040        return []
1041
1042    @property
1043    def selects(self) -> t.List[Expression]:
1044        if isinstance(self.expression, Subqueryable):
1045            return self.expression.selects
1046        return []
1047
1048
1049class DML(Expression):
1050    def returning(
1051        self,
1052        expression: ExpOrStr,
1053        dialect: DialectType = None,
1054        copy: bool = True,
1055        **opts,
1056    ) -> DML:
1057        """
1058        Set the RETURNING expression. Not supported by all dialects.
1059
1060        Example:
1061            >>> delete("tbl").returning("*", dialect="postgres").sql()
1062            'DELETE FROM tbl RETURNING *'
1063
1064        Args:
1065            expression: the SQL code strings to parse.
1066                If an `Expression` instance is passed, it will be used as-is.
1067            dialect: the dialect used to parse the input expressions.
1068            copy: if `False`, modify this expression instance in-place.
1069            opts: other options to use to parse the input expressions.
1070
1071        Returns:
1072            Delete: the modified expression.
1073        """
1074        return _apply_builder(
1075            expression=expression,
1076            instance=self,
1077            arg="returning",
1078            prefix="RETURNING",
1079            dialect=dialect,
1080            copy=copy,
1081            into=Returning,
1082            **opts,
1083        )
1084
1085
1086class Create(DDL):
1087    arg_types = {
1088        "with": False,
1089        "this": True,
1090        "kind": True,
1091        "expression": False,
1092        "exists": False,
1093        "properties": False,
1094        "replace": False,
1095        "unique": False,
1096        "indexes": False,
1097        "no_schema_binding": False,
1098        "begin": False,
1099        "end": False,
1100        "clone": False,
1101    }
1102
1103
1104# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1105# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1106# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1107class Clone(Expression):
1108    arg_types = {
1109        "this": True,
1110        "when": False,
1111        "kind": False,
1112        "shallow": False,
1113        "expression": False,
1114        "copy": False,
1115    }
1116
1117
1118class Describe(Expression):
1119    arg_types = {"this": True, "kind": False, "expressions": False}
1120
1121
1122class Kill(Expression):
1123    arg_types = {"this": True, "kind": False}
1124
1125
1126class Pragma(Expression):
1127    pass
1128
1129
1130class Set(Expression):
1131    arg_types = {"expressions": False, "unset": False, "tag": False}
1132
1133
1134class SetItem(Expression):
1135    arg_types = {
1136        "this": False,
1137        "expressions": False,
1138        "kind": False,
1139        "collate": False,  # MySQL SET NAMES statement
1140        "global": False,
1141    }
1142
1143
1144class Show(Expression):
1145    arg_types = {
1146        "this": True,
1147        "target": False,
1148        "offset": False,
1149        "limit": False,
1150        "like": False,
1151        "where": False,
1152        "db": False,
1153        "scope": False,
1154        "scope_kind": False,
1155        "full": False,
1156        "mutex": False,
1157        "query": False,
1158        "channel": False,
1159        "global": False,
1160        "log": False,
1161        "position": False,
1162        "types": False,
1163    }
1164
1165
1166class UserDefinedFunction(Expression):
1167    arg_types = {"this": True, "expressions": False, "wrapped": False}
1168
1169
1170class CharacterSet(Expression):
1171    arg_types = {"this": True, "default": False}
1172
1173
1174class With(Expression):
1175    arg_types = {"expressions": True, "recursive": False}
1176
1177    @property
1178    def recursive(self) -> bool:
1179        return bool(self.args.get("recursive"))
1180
1181
1182class WithinGroup(Expression):
1183    arg_types = {"this": True, "expression": False}
1184
1185
1186# clickhouse supports scalar ctes
1187# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1188class CTE(DerivedTable):
1189    arg_types = {"this": True, "alias": True, "scalar": False}
1190
1191
1192class TableAlias(Expression):
1193    arg_types = {"this": False, "columns": False}
1194
1195    @property
1196    def columns(self):
1197        return self.args.get("columns") or []
1198
1199
1200class BitString(Condition):
1201    pass
1202
1203
1204class HexString(Condition):
1205    pass
1206
1207
1208class ByteString(Condition):
1209    pass
1210
1211
1212class RawString(Condition):
1213    pass
1214
1215
1216class Column(Condition):
1217    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1218
1219    @property
1220    def table(self) -> str:
1221        return self.text("table")
1222
1223    @property
1224    def db(self) -> str:
1225        return self.text("db")
1226
1227    @property
1228    def catalog(self) -> str:
1229        return self.text("catalog")
1230
1231    @property
1232    def output_name(self) -> str:
1233        return self.name
1234
1235    @property
1236    def parts(self) -> t.List[Identifier]:
1237        """Return the parts of a column in order catalog, db, table, name."""
1238        return [
1239            t.cast(Identifier, self.args[part])
1240            for part in ("catalog", "db", "table", "this")
1241            if self.args.get(part)
1242        ]
1243
1244    def to_dot(self) -> Dot | Identifier:
1245        """Converts the column into a dot expression."""
1246        parts = self.parts
1247        parent = self.parent
1248
1249        while parent:
1250            if isinstance(parent, Dot):
1251                parts.append(parent.expression)
1252            parent = parent.parent
1253
1254        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1255
1256
1257class ColumnPosition(Expression):
1258    arg_types = {"this": False, "position": True}
1259
1260
1261class ColumnDef(Expression):
1262    arg_types = {
1263        "this": True,
1264        "kind": False,
1265        "constraints": False,
1266        "exists": False,
1267        "position": False,
1268    }
1269
1270    @property
1271    def constraints(self) -> t.List[ColumnConstraint]:
1272        return self.args.get("constraints") or []
1273
1274
1275class AlterColumn(Expression):
1276    arg_types = {
1277        "this": True,
1278        "dtype": False,
1279        "collate": False,
1280        "using": False,
1281        "default": False,
1282        "drop": False,
1283    }
1284
1285
1286class RenameTable(Expression):
1287    pass
1288
1289
1290class SwapTable(Expression):
1291    pass
1292
1293
1294class Comment(Expression):
1295    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1296
1297
1298class Comprehension(Expression):
1299    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1300
1301
1302# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1303class MergeTreeTTLAction(Expression):
1304    arg_types = {
1305        "this": True,
1306        "delete": False,
1307        "recompress": False,
1308        "to_disk": False,
1309        "to_volume": False,
1310    }
1311
1312
1313# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1314class MergeTreeTTL(Expression):
1315    arg_types = {
1316        "expressions": True,
1317        "where": False,
1318        "group": False,
1319        "aggregates": False,
1320    }
1321
1322
1323# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1324class IndexConstraintOption(Expression):
1325    arg_types = {
1326        "key_block_size": False,
1327        "using": False,
1328        "parser": False,
1329        "comment": False,
1330        "visible": False,
1331        "engine_attr": False,
1332        "secondary_engine_attr": False,
1333    }
1334
1335
1336class ColumnConstraint(Expression):
1337    arg_types = {"this": False, "kind": True}
1338
1339    @property
1340    def kind(self) -> ColumnConstraintKind:
1341        return self.args["kind"]
1342
1343
1344class ColumnConstraintKind(Expression):
1345    pass
1346
1347
1348class AutoIncrementColumnConstraint(ColumnConstraintKind):
1349    pass
1350
1351
1352class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1353    arg_types = {"this": True, "expression": True}
1354
1355
1356class CaseSpecificColumnConstraint(ColumnConstraintKind):
1357    arg_types = {"not_": True}
1358
1359
1360class CharacterSetColumnConstraint(ColumnConstraintKind):
1361    arg_types = {"this": True}
1362
1363
1364class CheckColumnConstraint(ColumnConstraintKind):
1365    pass
1366
1367
1368class ClusteredColumnConstraint(ColumnConstraintKind):
1369    pass
1370
1371
1372class CollateColumnConstraint(ColumnConstraintKind):
1373    pass
1374
1375
1376class CommentColumnConstraint(ColumnConstraintKind):
1377    pass
1378
1379
1380class CompressColumnConstraint(ColumnConstraintKind):
1381    pass
1382
1383
1384class DateFormatColumnConstraint(ColumnConstraintKind):
1385    arg_types = {"this": True}
1386
1387
1388class DefaultColumnConstraint(ColumnConstraintKind):
1389    pass
1390
1391
1392class EncodeColumnConstraint(ColumnConstraintKind):
1393    pass
1394
1395
1396class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1397    # this: True -> ALWAYS, this: False -> BY DEFAULT
1398    arg_types = {
1399        "this": False,
1400        "expression": False,
1401        "on_null": False,
1402        "start": False,
1403        "increment": False,
1404        "minvalue": False,
1405        "maxvalue": False,
1406        "cycle": False,
1407    }
1408
1409
1410class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1411    arg_types = {"start": True, "hidden": False}
1412
1413
1414# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1415class IndexColumnConstraint(ColumnConstraintKind):
1416    arg_types = {
1417        "this": False,
1418        "schema": True,
1419        "kind": False,
1420        "index_type": False,
1421        "options": False,
1422    }
1423
1424
1425class InlineLengthColumnConstraint(ColumnConstraintKind):
1426    pass
1427
1428
1429class NonClusteredColumnConstraint(ColumnConstraintKind):
1430    pass
1431
1432
1433class NotForReplicationColumnConstraint(ColumnConstraintKind):
1434    arg_types = {}
1435
1436
1437class NotNullColumnConstraint(ColumnConstraintKind):
1438    arg_types = {"allow_null": False}
1439
1440
1441# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1442class OnUpdateColumnConstraint(ColumnConstraintKind):
1443    pass
1444
1445
1446class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1447    arg_types = {"desc": False}
1448
1449
1450class TitleColumnConstraint(ColumnConstraintKind):
1451    pass
1452
1453
1454class UniqueColumnConstraint(ColumnConstraintKind):
1455    arg_types = {"this": False, "index_type": False}
1456
1457
1458class UppercaseColumnConstraint(ColumnConstraintKind):
1459    arg_types: t.Dict[str, t.Any] = {}
1460
1461
1462class PathColumnConstraint(ColumnConstraintKind):
1463    pass
1464
1465
1466# computed column expression
1467# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1468class ComputedColumnConstraint(ColumnConstraintKind):
1469    arg_types = {"this": True, "persisted": False, "not_null": False}
1470
1471
1472class Constraint(Expression):
1473    arg_types = {"this": True, "expressions": True}
1474
1475
1476class Delete(DML):
1477    arg_types = {
1478        "with": False,
1479        "this": False,
1480        "using": False,
1481        "where": False,
1482        "returning": False,
1483        "limit": False,
1484        "tables": False,  # Multiple-Table Syntax (MySQL)
1485    }
1486
1487    def delete(
1488        self,
1489        table: ExpOrStr,
1490        dialect: DialectType = None,
1491        copy: bool = True,
1492        **opts,
1493    ) -> Delete:
1494        """
1495        Create a DELETE expression or replace the table on an existing DELETE expression.
1496
1497        Example:
1498            >>> delete("tbl").sql()
1499            'DELETE FROM tbl'
1500
1501        Args:
1502            table: the table from which to delete.
1503            dialect: the dialect used to parse the input expression.
1504            copy: if `False`, modify this expression instance in-place.
1505            opts: other options to use to parse the input expressions.
1506
1507        Returns:
1508            Delete: the modified expression.
1509        """
1510        return _apply_builder(
1511            expression=table,
1512            instance=self,
1513            arg="this",
1514            dialect=dialect,
1515            into=Table,
1516            copy=copy,
1517            **opts,
1518        )
1519
1520    def where(
1521        self,
1522        *expressions: t.Optional[ExpOrStr],
1523        append: bool = True,
1524        dialect: DialectType = None,
1525        copy: bool = True,
1526        **opts,
1527    ) -> Delete:
1528        """
1529        Append to or set the WHERE expressions.
1530
1531        Example:
1532            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1533            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1534
1535        Args:
1536            *expressions: the SQL code strings to parse.
1537                If an `Expression` instance is passed, it will be used as-is.
1538                Multiple expressions are combined with an AND operator.
1539            append: if `True`, AND the new expressions to any existing expression.
1540                Otherwise, this resets the expression.
1541            dialect: the dialect used to parse the input expressions.
1542            copy: if `False`, modify this expression instance in-place.
1543            opts: other options to use to parse the input expressions.
1544
1545        Returns:
1546            Delete: the modified expression.
1547        """
1548        return _apply_conjunction_builder(
1549            *expressions,
1550            instance=self,
1551            arg="where",
1552            append=append,
1553            into=Where,
1554            dialect=dialect,
1555            copy=copy,
1556            **opts,
1557        )
1558
1559
1560class Drop(Expression):
1561    arg_types = {
1562        "this": False,
1563        "kind": False,
1564        "exists": False,
1565        "temporary": False,
1566        "materialized": False,
1567        "cascade": False,
1568        "constraints": False,
1569        "purge": False,
1570    }
1571
1572
1573class Filter(Expression):
1574    arg_types = {"this": True, "expression": True}
1575
1576
1577class Check(Expression):
1578    pass
1579
1580
1581# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1582class Connect(Expression):
1583    arg_types = {"start": False, "connect": True}
1584
1585
1586class Prior(Expression):
1587    pass
1588
1589
1590class Directory(Expression):
1591    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1592    arg_types = {"this": True, "local": False, "row_format": False}
1593
1594
1595class ForeignKey(Expression):
1596    arg_types = {
1597        "expressions": True,
1598        "reference": False,
1599        "delete": False,
1600        "update": False,
1601    }
1602
1603
1604class ColumnPrefix(Expression):
1605    arg_types = {"this": True, "expression": True}
1606
1607
1608class PrimaryKey(Expression):
1609    arg_types = {"expressions": True, "options": False}
1610
1611
1612# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1613# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1614class Into(Expression):
1615    arg_types = {"this": True, "temporary": False, "unlogged": False}
1616
1617
1618class From(Expression):
1619    @property
1620    def name(self) -> str:
1621        return self.this.name
1622
1623    @property
1624    def alias_or_name(self) -> str:
1625        return self.this.alias_or_name
1626
1627
1628class Having(Expression):
1629    pass
1630
1631
1632class Hint(Expression):
1633    arg_types = {"expressions": True}
1634
1635
1636class JoinHint(Expression):
1637    arg_types = {"this": True, "expressions": True}
1638
1639
1640class Identifier(Expression):
1641    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1642
1643    @property
1644    def quoted(self) -> bool:
1645        return bool(self.args.get("quoted"))
1646
1647    @property
1648    def hashable_args(self) -> t.Any:
1649        return (self.this, self.quoted)
1650
1651    @property
1652    def output_name(self) -> str:
1653        return self.name
1654
1655
1656# https://www.postgresql.org/docs/current/indexes-opclass.html
1657class Opclass(Expression):
1658    arg_types = {"this": True, "expression": True}
1659
1660
1661class Index(Expression):
1662    arg_types = {
1663        "this": False,
1664        "table": False,
1665        "using": False,
1666        "where": False,
1667        "columns": False,
1668        "unique": False,
1669        "primary": False,
1670        "amp": False,  # teradata
1671        "partition_by": False,  # teradata
1672        "where": False,  # postgres partial indexes
1673    }
1674
1675
1676class Insert(DDL, DML):
1677    arg_types = {
1678        "with": False,
1679        "this": True,
1680        "expression": False,
1681        "conflict": False,
1682        "returning": False,
1683        "overwrite": False,
1684        "exists": False,
1685        "partition": False,
1686        "alternative": False,
1687        "where": False,
1688        "ignore": False,
1689        "by_name": False,
1690    }
1691
1692    def with_(
1693        self,
1694        alias: ExpOrStr,
1695        as_: ExpOrStr,
1696        recursive: t.Optional[bool] = None,
1697        append: bool = True,
1698        dialect: DialectType = None,
1699        copy: bool = True,
1700        **opts,
1701    ) -> Insert:
1702        """
1703        Append to or set the common table expressions.
1704
1705        Example:
1706            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1707            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1708
1709        Args:
1710            alias: the SQL code string to parse as the table name.
1711                If an `Expression` instance is passed, this is used as-is.
1712            as_: the SQL code string to parse as the table expression.
1713                If an `Expression` instance is passed, it will be used as-is.
1714            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1715            append: if `True`, add to any existing expressions.
1716                Otherwise, this resets the expressions.
1717            dialect: the dialect used to parse the input expression.
1718            copy: if `False`, modify this expression instance in-place.
1719            opts: other options to use to parse the input expressions.
1720
1721        Returns:
1722            The modified expression.
1723        """
1724        return _apply_cte_builder(
1725            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1726        )
1727
1728
1729class OnConflict(Expression):
1730    arg_types = {
1731        "duplicate": False,
1732        "expressions": False,
1733        "nothing": False,
1734        "key": False,
1735        "constraint": False,
1736    }
1737
1738
1739class Returning(Expression):
1740    arg_types = {"expressions": True, "into": False}
1741
1742
1743# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1744class Introducer(Expression):
1745    arg_types = {"this": True, "expression": True}
1746
1747
1748# national char, like n'utf8'
1749class National(Expression):
1750    pass
1751
1752
1753class LoadData(Expression):
1754    arg_types = {
1755        "this": True,
1756        "local": False,
1757        "overwrite": False,
1758        "inpath": True,
1759        "partition": False,
1760        "input_format": False,
1761        "serde": False,
1762    }
1763
1764
1765class Partition(Expression):
1766    arg_types = {"expressions": True}
1767
1768
1769class Fetch(Expression):
1770    arg_types = {
1771        "direction": False,
1772        "count": False,
1773        "percent": False,
1774        "with_ties": False,
1775    }
1776
1777
1778class Group(Expression):
1779    arg_types = {
1780        "expressions": False,
1781        "grouping_sets": False,
1782        "cube": False,
1783        "rollup": False,
1784        "totals": False,
1785        "all": False,
1786    }
1787
1788
1789class Lambda(Expression):
1790    arg_types = {"this": True, "expressions": True}
1791
1792
1793class Limit(Expression):
1794    arg_types = {"this": False, "expression": True, "offset": False}
1795
1796
1797class Literal(Condition):
1798    arg_types = {"this": True, "is_string": True}
1799
1800    @property
1801    def hashable_args(self) -> t.Any:
1802        return (self.this, self.args.get("is_string"))
1803
1804    @classmethod
1805    def number(cls, number) -> Literal:
1806        return cls(this=str(number), is_string=False)
1807
1808    @classmethod
1809    def string(cls, string) -> Literal:
1810        return cls(this=str(string), is_string=True)
1811
1812    @property
1813    def output_name(self) -> str:
1814        return self.name
1815
1816
1817class Join(Expression):
1818    arg_types = {
1819        "this": True,
1820        "on": False,
1821        "side": False,
1822        "kind": False,
1823        "using": False,
1824        "method": False,
1825        "global": False,
1826        "hint": False,
1827    }
1828
1829    @property
1830    def method(self) -> str:
1831        return self.text("method").upper()
1832
1833    @property
1834    def kind(self) -> str:
1835        return self.text("kind").upper()
1836
1837    @property
1838    def side(self) -> str:
1839        return self.text("side").upper()
1840
1841    @property
1842    def hint(self) -> str:
1843        return self.text("hint").upper()
1844
1845    @property
1846    def alias_or_name(self) -> str:
1847        return self.this.alias_or_name
1848
1849    def on(
1850        self,
1851        *expressions: t.Optional[ExpOrStr],
1852        append: bool = True,
1853        dialect: DialectType = None,
1854        copy: bool = True,
1855        **opts,
1856    ) -> Join:
1857        """
1858        Append to or set the ON expressions.
1859
1860        Example:
1861            >>> import sqlglot
1862            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1863            'JOIN x ON y = 1'
1864
1865        Args:
1866            *expressions: the SQL code strings to parse.
1867                If an `Expression` instance is passed, it will be used as-is.
1868                Multiple expressions are combined with an AND operator.
1869            append: if `True`, AND the new expressions to any existing expression.
1870                Otherwise, this resets the expression.
1871            dialect: the dialect used to parse the input expressions.
1872            copy: if `False`, modify this expression instance in-place.
1873            opts: other options to use to parse the input expressions.
1874
1875        Returns:
1876            The modified Join expression.
1877        """
1878        join = _apply_conjunction_builder(
1879            *expressions,
1880            instance=self,
1881            arg="on",
1882            append=append,
1883            dialect=dialect,
1884            copy=copy,
1885            **opts,
1886        )
1887
1888        if join.kind == "CROSS":
1889            join.set("kind", None)
1890
1891        return join
1892
1893    def using(
1894        self,
1895        *expressions: t.Optional[ExpOrStr],
1896        append: bool = True,
1897        dialect: DialectType = None,
1898        copy: bool = True,
1899        **opts,
1900    ) -> Join:
1901        """
1902        Append to or set the USING expressions.
1903
1904        Example:
1905            >>> import sqlglot
1906            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1907            'JOIN x USING (foo, bla)'
1908
1909        Args:
1910            *expressions: the SQL code strings to parse.
1911                If an `Expression` instance is passed, it will be used as-is.
1912            append: if `True`, concatenate the new expressions to the existing "using" list.
1913                Otherwise, this resets the expression.
1914            dialect: the dialect used to parse the input expressions.
1915            copy: if `False`, modify this expression instance in-place.
1916            opts: other options to use to parse the input expressions.
1917
1918        Returns:
1919            The modified Join expression.
1920        """
1921        join = _apply_list_builder(
1922            *expressions,
1923            instance=self,
1924            arg="using",
1925            append=append,
1926            dialect=dialect,
1927            copy=copy,
1928            **opts,
1929        )
1930
1931        if join.kind == "CROSS":
1932            join.set("kind", None)
1933
1934        return join
1935
1936
1937class Lateral(UDTF):
1938    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1939
1940
1941class MatchRecognize(Expression):
1942    arg_types = {
1943        "partition_by": False,
1944        "order": False,
1945        "measures": False,
1946        "rows": False,
1947        "after": False,
1948        "pattern": False,
1949        "define": False,
1950        "alias": False,
1951    }
1952
1953
1954# Clickhouse FROM FINAL modifier
1955# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1956class Final(Expression):
1957    pass
1958
1959
1960class Offset(Expression):
1961    arg_types = {"this": False, "expression": True}
1962
1963
1964class Order(Expression):
1965    arg_types = {"this": False, "expressions": True}
1966
1967
1968# hive specific sorts
1969# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1970class Cluster(Order):
1971    pass
1972
1973
1974class Distribute(Order):
1975    pass
1976
1977
1978class Sort(Order):
1979    pass
1980
1981
1982class Ordered(Expression):
1983    arg_types = {"this": True, "desc": False, "nulls_first": True}
1984
1985
1986class Property(Expression):
1987    arg_types = {"this": True, "value": True}
1988
1989
1990class AlgorithmProperty(Property):
1991    arg_types = {"this": True}
1992
1993
1994class AutoIncrementProperty(Property):
1995    arg_types = {"this": True}
1996
1997
1998class BlockCompressionProperty(Property):
1999    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
2000
2001
2002class CharacterSetProperty(Property):
2003    arg_types = {"this": True, "default": True}
2004
2005
2006class ChecksumProperty(Property):
2007    arg_types = {"on": False, "default": False}
2008
2009
2010class CollateProperty(Property):
2011    arg_types = {"this": True, "default": False}
2012
2013
2014class CopyGrantsProperty(Property):
2015    arg_types = {}
2016
2017
2018class DataBlocksizeProperty(Property):
2019    arg_types = {
2020        "size": False,
2021        "units": False,
2022        "minimum": False,
2023        "maximum": False,
2024        "default": False,
2025    }
2026
2027
2028class DefinerProperty(Property):
2029    arg_types = {"this": True}
2030
2031
2032class DistKeyProperty(Property):
2033    arg_types = {"this": True}
2034
2035
2036class DistStyleProperty(Property):
2037    arg_types = {"this": True}
2038
2039
2040class EngineProperty(Property):
2041    arg_types = {"this": True}
2042
2043
2044class HeapProperty(Property):
2045    arg_types = {}
2046
2047
2048class ToTableProperty(Property):
2049    arg_types = {"this": True}
2050
2051
2052class ExecuteAsProperty(Property):
2053    arg_types = {"this": True}
2054
2055
2056class ExternalProperty(Property):
2057    arg_types = {"this": False}
2058
2059
2060class FallbackProperty(Property):
2061    arg_types = {"no": True, "protection": False}
2062
2063
2064class FileFormatProperty(Property):
2065    arg_types = {"this": True}
2066
2067
2068class FreespaceProperty(Property):
2069    arg_types = {"this": True, "percent": False}
2070
2071
2072class InputModelProperty(Property):
2073    arg_types = {"this": True}
2074
2075
2076class OutputModelProperty(Property):
2077    arg_types = {"this": True}
2078
2079
2080class IsolatedLoadingProperty(Property):
2081    arg_types = {
2082        "no": True,
2083        "concurrent": True,
2084        "for_all": True,
2085        "for_insert": True,
2086        "for_none": True,
2087    }
2088
2089
2090class JournalProperty(Property):
2091    arg_types = {
2092        "no": False,
2093        "dual": False,
2094        "before": False,
2095        "local": False,
2096        "after": False,
2097    }
2098
2099
2100class LanguageProperty(Property):
2101    arg_types = {"this": True}
2102
2103
2104# spark ddl
2105class ClusteredByProperty(Property):
2106    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2107
2108
2109class DictProperty(Property):
2110    arg_types = {"this": True, "kind": True, "settings": False}
2111
2112
2113class DictSubProperty(Property):
2114    pass
2115
2116
2117class DictRange(Property):
2118    arg_types = {"this": True, "min": True, "max": True}
2119
2120
2121# Clickhouse CREATE ... ON CLUSTER modifier
2122# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2123class OnCluster(Property):
2124    arg_types = {"this": True}
2125
2126
2127class LikeProperty(Property):
2128    arg_types = {"this": True, "expressions": False}
2129
2130
2131class LocationProperty(Property):
2132    arg_types = {"this": True}
2133
2134
2135class LockingProperty(Property):
2136    arg_types = {
2137        "this": False,
2138        "kind": True,
2139        "for_or_in": False,
2140        "lock_type": True,
2141        "override": False,
2142    }
2143
2144
2145class LogProperty(Property):
2146    arg_types = {"no": True}
2147
2148
2149class MaterializedProperty(Property):
2150    arg_types = {"this": False}
2151
2152
2153class MergeBlockRatioProperty(Property):
2154    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2155
2156
2157class NoPrimaryIndexProperty(Property):
2158    arg_types = {}
2159
2160
2161class OnProperty(Property):
2162    arg_types = {"this": True}
2163
2164
2165class OnCommitProperty(Property):
2166    arg_types = {"delete": False}
2167
2168
2169class PartitionedByProperty(Property):
2170    arg_types = {"this": True}
2171
2172
2173# https://www.postgresql.org/docs/current/sql-createtable.html
2174class PartitionBoundSpec(Expression):
2175    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2176    arg_types = {
2177        "this": False,
2178        "expression": False,
2179        "from_expressions": False,
2180        "to_expressions": False,
2181    }
2182
2183
2184class PartitionedOfProperty(Property):
2185    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2186    arg_types = {"this": True, "expression": True}
2187
2188
2189class RemoteWithConnectionModelProperty(Property):
2190    arg_types = {"this": True}
2191
2192
2193class ReturnsProperty(Property):
2194    arg_types = {"this": True, "is_table": False, "table": False}
2195
2196
2197class RowFormatProperty(Property):
2198    arg_types = {"this": True}
2199
2200
2201class RowFormatDelimitedProperty(Property):
2202    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2203    arg_types = {
2204        "fields": False,
2205        "escaped": False,
2206        "collection_items": False,
2207        "map_keys": False,
2208        "lines": False,
2209        "null": False,
2210        "serde": False,
2211    }
2212
2213
2214class RowFormatSerdeProperty(Property):
2215    arg_types = {"this": True, "serde_properties": False}
2216
2217
2218# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2219class QueryTransform(Expression):
2220    arg_types = {
2221        "expressions": True,
2222        "command_script": True,
2223        "schema": False,
2224        "row_format_before": False,
2225        "record_writer": False,
2226        "row_format_after": False,
2227        "record_reader": False,
2228    }
2229
2230
2231class SampleProperty(Property):
2232    arg_types = {"this": True}
2233
2234
2235class SchemaCommentProperty(Property):
2236    arg_types = {"this": True}
2237
2238
2239class SerdeProperties(Property):
2240    arg_types = {"expressions": True}
2241
2242
2243class SetProperty(Property):
2244    arg_types = {"multi": True}
2245
2246
2247class SettingsProperty(Property):
2248    arg_types = {"expressions": True}
2249
2250
2251class SortKeyProperty(Property):
2252    arg_types = {"this": True, "compound": False}
2253
2254
2255class SqlSecurityProperty(Property):
2256    arg_types = {"definer": True}
2257
2258
2259class StabilityProperty(Property):
2260    arg_types = {"this": True}
2261
2262
2263class TemporaryProperty(Property):
2264    arg_types = {}
2265
2266
2267class TransformModelProperty(Property):
2268    arg_types = {"expressions": True}
2269
2270
2271class TransientProperty(Property):
2272    arg_types = {"this": False}
2273
2274
2275class VolatileProperty(Property):
2276    arg_types = {"this": False}
2277
2278
2279class WithDataProperty(Property):
2280    arg_types = {"no": True, "statistics": False}
2281
2282
2283class WithJournalTableProperty(Property):
2284    arg_types = {"this": True}
2285
2286
2287class WithSystemVersioningProperty(Property):
2288    # this -> history table name, expression -> data consistency check
2289    arg_types = {"this": False, "expression": False}
2290
2291
2292class Properties(Expression):
2293    arg_types = {"expressions": True}
2294
2295    NAME_TO_PROPERTY = {
2296        "ALGORITHM": AlgorithmProperty,
2297        "AUTO_INCREMENT": AutoIncrementProperty,
2298        "CHARACTER SET": CharacterSetProperty,
2299        "CLUSTERED_BY": ClusteredByProperty,
2300        "COLLATE": CollateProperty,
2301        "COMMENT": SchemaCommentProperty,
2302        "DEFINER": DefinerProperty,
2303        "DISTKEY": DistKeyProperty,
2304        "DISTSTYLE": DistStyleProperty,
2305        "ENGINE": EngineProperty,
2306        "EXECUTE AS": ExecuteAsProperty,
2307        "FORMAT": FileFormatProperty,
2308        "LANGUAGE": LanguageProperty,
2309        "LOCATION": LocationProperty,
2310        "PARTITIONED_BY": PartitionedByProperty,
2311        "RETURNS": ReturnsProperty,
2312        "ROW_FORMAT": RowFormatProperty,
2313        "SORTKEY": SortKeyProperty,
2314    }
2315
2316    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2317
2318    # CREATE property locations
2319    # Form: schema specified
2320    #   create [POST_CREATE]
2321    #     table a [POST_NAME]
2322    #     (b int) [POST_SCHEMA]
2323    #     with ([POST_WITH])
2324    #     index (b) [POST_INDEX]
2325    #
2326    # Form: alias selection
2327    #   create [POST_CREATE]
2328    #     table a [POST_NAME]
2329    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2330    #     index (c) [POST_INDEX]
2331    class Location(AutoName):
2332        POST_CREATE = auto()
2333        POST_NAME = auto()
2334        POST_SCHEMA = auto()
2335        POST_WITH = auto()
2336        POST_ALIAS = auto()
2337        POST_EXPRESSION = auto()
2338        POST_INDEX = auto()
2339        UNSUPPORTED = auto()
2340
2341    @classmethod
2342    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2343        expressions = []
2344        for key, value in properties_dict.items():
2345            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2346            if property_cls:
2347                expressions.append(property_cls(this=convert(value)))
2348            else:
2349                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2350
2351        return cls(expressions=expressions)
2352
2353
2354class Qualify(Expression):
2355    pass
2356
2357
2358class InputOutputFormat(Expression):
2359    arg_types = {"input_format": False, "output_format": False}
2360
2361
2362# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2363class Return(Expression):
2364    pass
2365
2366
2367class Reference(Expression):
2368    arg_types = {"this": True, "expressions": False, "options": False}
2369
2370
2371class Tuple(Expression):
2372    arg_types = {"expressions": False}
2373
2374    def isin(
2375        self,
2376        *expressions: t.Any,
2377        query: t.Optional[ExpOrStr] = None,
2378        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2379        copy: bool = True,
2380        **opts,
2381    ) -> In:
2382        return In(
2383            this=maybe_copy(self, copy),
2384            expressions=[convert(e, copy=copy) for e in expressions],
2385            query=maybe_parse(query, copy=copy, **opts) if query else None,
2386            unnest=Unnest(
2387                expressions=[
2388                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2389                ]
2390            )
2391            if unnest
2392            else None,
2393        )
2394
2395
2396class Subqueryable(Unionable):
2397    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2398        """
2399        Convert this expression to an aliased expression that can be used as a Subquery.
2400
2401        Example:
2402            >>> subquery = Select().select("x").from_("tbl").subquery()
2403            >>> Select().select("x").from_(subquery).sql()
2404            'SELECT x FROM (SELECT x FROM tbl)'
2405
2406        Args:
2407            alias (str | Identifier): an optional alias for the subquery
2408            copy (bool): if `False`, modify this expression instance in-place.
2409
2410        Returns:
2411            Alias: the subquery
2412        """
2413        instance = maybe_copy(self, copy)
2414        if not isinstance(alias, Expression):
2415            alias = TableAlias(this=to_identifier(alias)) if alias else None
2416
2417        return Subquery(this=instance, alias=alias)
2418
2419    def limit(
2420        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2421    ) -> Select:
2422        raise NotImplementedError
2423
2424    @property
2425    def ctes(self):
2426        with_ = self.args.get("with")
2427        if not with_:
2428            return []
2429        return with_.expressions
2430
2431    @property
2432    def selects(self) -> t.List[Expression]:
2433        raise NotImplementedError("Subqueryable objects must implement `selects`")
2434
2435    @property
2436    def named_selects(self) -> t.List[str]:
2437        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2438
2439    def select(
2440        self,
2441        *expressions: t.Optional[ExpOrStr],
2442        append: bool = True,
2443        dialect: DialectType = None,
2444        copy: bool = True,
2445        **opts,
2446    ) -> Subqueryable:
2447        raise NotImplementedError("Subqueryable objects must implement `select`")
2448
2449    def with_(
2450        self,
2451        alias: ExpOrStr,
2452        as_: ExpOrStr,
2453        recursive: t.Optional[bool] = None,
2454        append: bool = True,
2455        dialect: DialectType = None,
2456        copy: bool = True,
2457        **opts,
2458    ) -> Subqueryable:
2459        """
2460        Append to or set the common table expressions.
2461
2462        Example:
2463            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2464            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2465
2466        Args:
2467            alias: the SQL code string to parse as the table name.
2468                If an `Expression` instance is passed, this is used as-is.
2469            as_: the SQL code string to parse as the table expression.
2470                If an `Expression` instance is passed, it will be used as-is.
2471            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2472            append: if `True`, add to any existing expressions.
2473                Otherwise, this resets the expressions.
2474            dialect: the dialect used to parse the input expression.
2475            copy: if `False`, modify this expression instance in-place.
2476            opts: other options to use to parse the input expressions.
2477
2478        Returns:
2479            The modified expression.
2480        """
2481        return _apply_cte_builder(
2482            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2483        )
2484
2485
2486QUERY_MODIFIERS = {
2487    "match": False,
2488    "laterals": False,
2489    "joins": False,
2490    "connect": False,
2491    "pivots": False,
2492    "where": False,
2493    "group": False,
2494    "having": False,
2495    "qualify": False,
2496    "windows": False,
2497    "distribute": False,
2498    "sort": False,
2499    "cluster": False,
2500    "order": False,
2501    "limit": False,
2502    "offset": False,
2503    "locks": False,
2504    "sample": False,
2505    "settings": False,
2506    "format": False,
2507}
2508
2509
2510# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2511class WithTableHint(Expression):
2512    arg_types = {"expressions": True}
2513
2514
2515# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2516class IndexTableHint(Expression):
2517    arg_types = {"this": True, "expressions": False, "target": False}
2518
2519
2520class Table(Expression):
2521    arg_types = {
2522        "this": True,
2523        "alias": False,
2524        "db": False,
2525        "catalog": False,
2526        "laterals": False,
2527        "joins": False,
2528        "pivots": False,
2529        "hints": False,
2530        "system_time": False,
2531        "version": False,
2532        "format": False,
2533        "pattern": False,
2534        "index": False,
2535        "ordinality": False,
2536    }
2537
2538    @property
2539    def name(self) -> str:
2540        if isinstance(self.this, Func):
2541            return ""
2542        return self.this.name
2543
2544    @property
2545    def db(self) -> str:
2546        return self.text("db")
2547
2548    @property
2549    def catalog(self) -> str:
2550        return self.text("catalog")
2551
2552    @property
2553    def selects(self) -> t.List[Expression]:
2554        return []
2555
2556    @property
2557    def named_selects(self) -> t.List[str]:
2558        return []
2559
2560    @property
2561    def parts(self) -> t.List[Expression]:
2562        """Return the parts of a table in order catalog, db, table."""
2563        parts: t.List[Expression] = []
2564
2565        for arg in ("catalog", "db", "this"):
2566            part = self.args.get(arg)
2567
2568            if isinstance(part, Dot):
2569                parts.extend(part.flatten())
2570            elif isinstance(part, Expression):
2571                parts.append(part)
2572
2573        return parts
2574
2575
2576class Union(Subqueryable):
2577    arg_types = {
2578        "with": False,
2579        "this": True,
2580        "expression": True,
2581        "distinct": False,
2582        "by_name": False,
2583        **QUERY_MODIFIERS,
2584    }
2585
2586    def limit(
2587        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2588    ) -> Select:
2589        """
2590        Set the LIMIT expression.
2591
2592        Example:
2593            >>> select("1").union(select("1")).limit(1).sql()
2594            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2595
2596        Args:
2597            expression: the SQL code string to parse.
2598                This can also be an integer.
2599                If a `Limit` instance is passed, this is used as-is.
2600                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2601            dialect: the dialect used to parse the input expression.
2602            copy: if `False`, modify this expression instance in-place.
2603            opts: other options to use to parse the input expressions.
2604
2605        Returns:
2606            The limited subqueryable.
2607        """
2608        return (
2609            select("*")
2610            .from_(self.subquery(alias="_l_0", copy=copy))
2611            .limit(expression, dialect=dialect, copy=False, **opts)
2612        )
2613
2614    def select(
2615        self,
2616        *expressions: t.Optional[ExpOrStr],
2617        append: bool = True,
2618        dialect: DialectType = None,
2619        copy: bool = True,
2620        **opts,
2621    ) -> Union:
2622        """Append to or set the SELECT of the union recursively.
2623
2624        Example:
2625            >>> from sqlglot import parse_one
2626            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2627            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2628
2629        Args:
2630            *expressions: the SQL code strings to parse.
2631                If an `Expression` instance is passed, it will be used as-is.
2632            append: if `True`, add to any existing expressions.
2633                Otherwise, this resets the expressions.
2634            dialect: the dialect used to parse the input expressions.
2635            copy: if `False`, modify this expression instance in-place.
2636            opts: other options to use to parse the input expressions.
2637
2638        Returns:
2639            Union: the modified expression.
2640        """
2641        this = self.copy() if copy else self
2642        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2643        this.expression.unnest().select(
2644            *expressions, append=append, dialect=dialect, copy=False, **opts
2645        )
2646        return this
2647
2648    @property
2649    def named_selects(self) -> t.List[str]:
2650        return self.this.unnest().named_selects
2651
2652    @property
2653    def is_star(self) -> bool:
2654        return self.this.is_star or self.expression.is_star
2655
2656    @property
2657    def selects(self) -> t.List[Expression]:
2658        return self.this.unnest().selects
2659
2660    @property
2661    def left(self) -> Expression:
2662        return self.this
2663
2664    @property
2665    def right(self) -> Expression:
2666        return self.expression
2667
2668
2669class Except(Union):
2670    pass
2671
2672
2673class Intersect(Union):
2674    pass
2675
2676
2677class Unnest(UDTF):
2678    arg_types = {
2679        "expressions": True,
2680        "alias": False,
2681        "offset": False,
2682    }
2683
2684
2685class Update(Expression):
2686    arg_types = {
2687        "with": False,
2688        "this": False,
2689        "expressions": True,
2690        "from": False,
2691        "where": False,
2692        "returning": False,
2693        "order": False,
2694        "limit": False,
2695    }
2696
2697
2698class Values(UDTF):
2699    arg_types = {"expressions": True, "alias": False}
2700
2701
2702class Var(Expression):
2703    pass
2704
2705
2706class Version(Expression):
2707    """
2708    Time travel, iceberg, bigquery etc
2709    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2710    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2711    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2712    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2713    this is either TIMESTAMP or VERSION
2714    kind is ("AS OF", "BETWEEN")
2715    """
2716
2717    arg_types = {"this": True, "kind": True, "expression": False}
2718
2719
2720class Schema(Expression):
2721    arg_types = {"this": False, "expressions": False}
2722
2723
2724# https://dev.mysql.com/doc/refman/8.0/en/select.html
2725# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2726class Lock(Expression):
2727    arg_types = {"update": True, "expressions": False, "wait": False}
2728
2729
2730class Select(Subqueryable):
2731    arg_types = {
2732        "with": False,
2733        "kind": False,
2734        "expressions": False,
2735        "hint": False,
2736        "distinct": False,
2737        "into": False,
2738        "from": False,
2739        **QUERY_MODIFIERS,
2740    }
2741
2742    def from_(
2743        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2744    ) -> Select:
2745        """
2746        Set the FROM expression.
2747
2748        Example:
2749            >>> Select().from_("tbl").select("x").sql()
2750            'SELECT x FROM tbl'
2751
2752        Args:
2753            expression : the SQL code strings to parse.
2754                If a `From` instance is passed, this is used as-is.
2755                If another `Expression` instance is passed, it will be wrapped in a `From`.
2756            dialect: the dialect used to parse the input expression.
2757            copy: if `False`, modify this expression instance in-place.
2758            opts: other options to use to parse the input expressions.
2759
2760        Returns:
2761            The modified Select expression.
2762        """
2763        return _apply_builder(
2764            expression=expression,
2765            instance=self,
2766            arg="from",
2767            into=From,
2768            prefix="FROM",
2769            dialect=dialect,
2770            copy=copy,
2771            **opts,
2772        )
2773
2774    def group_by(
2775        self,
2776        *expressions: t.Optional[ExpOrStr],
2777        append: bool = True,
2778        dialect: DialectType = None,
2779        copy: bool = True,
2780        **opts,
2781    ) -> Select:
2782        """
2783        Set the GROUP BY expression.
2784
2785        Example:
2786            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2787            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2788
2789        Args:
2790            *expressions: the SQL code strings to parse.
2791                If a `Group` instance is passed, this is used as-is.
2792                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2793                If nothing is passed in then a group by is not applied to the expression
2794            append: if `True`, add to any existing expressions.
2795                Otherwise, this flattens all the `Group` expression into a single expression.
2796            dialect: the dialect used to parse the input expression.
2797            copy: if `False`, modify this expression instance in-place.
2798            opts: other options to use to parse the input expressions.
2799
2800        Returns:
2801            The modified Select expression.
2802        """
2803        if not expressions:
2804            return self if not copy else self.copy()
2805
2806        return _apply_child_list_builder(
2807            *expressions,
2808            instance=self,
2809            arg="group",
2810            append=append,
2811            copy=copy,
2812            prefix="GROUP BY",
2813            into=Group,
2814            dialect=dialect,
2815            **opts,
2816        )
2817
2818    def order_by(
2819        self,
2820        *expressions: t.Optional[ExpOrStr],
2821        append: bool = True,
2822        dialect: DialectType = None,
2823        copy: bool = True,
2824        **opts,
2825    ) -> Select:
2826        """
2827        Set the ORDER BY expression.
2828
2829        Example:
2830            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2831            'SELECT x FROM tbl ORDER BY x DESC'
2832
2833        Args:
2834            *expressions: the SQL code strings to parse.
2835                If a `Group` instance is passed, this is used as-is.
2836                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2837            append: if `True`, add to any existing expressions.
2838                Otherwise, this flattens all the `Order` expression into a single expression.
2839            dialect: the dialect used to parse the input expression.
2840            copy: if `False`, modify this expression instance in-place.
2841            opts: other options to use to parse the input expressions.
2842
2843        Returns:
2844            The modified Select expression.
2845        """
2846        return _apply_child_list_builder(
2847            *expressions,
2848            instance=self,
2849            arg="order",
2850            append=append,
2851            copy=copy,
2852            prefix="ORDER BY",
2853            into=Order,
2854            dialect=dialect,
2855            **opts,
2856        )
2857
2858    def sort_by(
2859        self,
2860        *expressions: t.Optional[ExpOrStr],
2861        append: bool = True,
2862        dialect: DialectType = None,
2863        copy: bool = True,
2864        **opts,
2865    ) -> Select:
2866        """
2867        Set the SORT BY expression.
2868
2869        Example:
2870            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2871            'SELECT x FROM tbl SORT BY x DESC'
2872
2873        Args:
2874            *expressions: the SQL code strings to parse.
2875                If a `Group` instance is passed, this is used as-is.
2876                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2877            append: if `True`, add to any existing expressions.
2878                Otherwise, this flattens all the `Order` expression into a single expression.
2879            dialect: the dialect used to parse the input expression.
2880            copy: if `False`, modify this expression instance in-place.
2881            opts: other options to use to parse the input expressions.
2882
2883        Returns:
2884            The modified Select expression.
2885        """
2886        return _apply_child_list_builder(
2887            *expressions,
2888            instance=self,
2889            arg="sort",
2890            append=append,
2891            copy=copy,
2892            prefix="SORT BY",
2893            into=Sort,
2894            dialect=dialect,
2895            **opts,
2896        )
2897
2898    def cluster_by(
2899        self,
2900        *expressions: t.Optional[ExpOrStr],
2901        append: bool = True,
2902        dialect: DialectType = None,
2903        copy: bool = True,
2904        **opts,
2905    ) -> Select:
2906        """
2907        Set the CLUSTER BY expression.
2908
2909        Example:
2910            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2911            'SELECT x FROM tbl CLUSTER BY x DESC'
2912
2913        Args:
2914            *expressions: the SQL code strings to parse.
2915                If a `Group` instance is passed, this is used as-is.
2916                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2917            append: if `True`, add to any existing expressions.
2918                Otherwise, this flattens all the `Order` expression into a single expression.
2919            dialect: the dialect used to parse the input expression.
2920            copy: if `False`, modify this expression instance in-place.
2921            opts: other options to use to parse the input expressions.
2922
2923        Returns:
2924            The modified Select expression.
2925        """
2926        return _apply_child_list_builder(
2927            *expressions,
2928            instance=self,
2929            arg="cluster",
2930            append=append,
2931            copy=copy,
2932            prefix="CLUSTER BY",
2933            into=Cluster,
2934            dialect=dialect,
2935            **opts,
2936        )
2937
2938    def limit(
2939        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2940    ) -> Select:
2941        """
2942        Set the LIMIT expression.
2943
2944        Example:
2945            >>> Select().from_("tbl").select("x").limit(10).sql()
2946            'SELECT x FROM tbl LIMIT 10'
2947
2948        Args:
2949            expression: the SQL code string to parse.
2950                This can also be an integer.
2951                If a `Limit` instance is passed, this is used as-is.
2952                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2953            dialect: the dialect used to parse the input expression.
2954            copy: if `False`, modify this expression instance in-place.
2955            opts: other options to use to parse the input expressions.
2956
2957        Returns:
2958            Select: the modified expression.
2959        """
2960        return _apply_builder(
2961            expression=expression,
2962            instance=self,
2963            arg="limit",
2964            into=Limit,
2965            prefix="LIMIT",
2966            dialect=dialect,
2967            copy=copy,
2968            into_arg="expression",
2969            **opts,
2970        )
2971
2972    def offset(
2973        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2974    ) -> Select:
2975        """
2976        Set the OFFSET expression.
2977
2978        Example:
2979            >>> Select().from_("tbl").select("x").offset(10).sql()
2980            'SELECT x FROM tbl OFFSET 10'
2981
2982        Args:
2983            expression: the SQL code string to parse.
2984                This can also be an integer.
2985                If a `Offset` instance is passed, this is used as-is.
2986                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2987            dialect: the dialect used to parse the input expression.
2988            copy: if `False`, modify this expression instance in-place.
2989            opts: other options to use to parse the input expressions.
2990
2991        Returns:
2992            The modified Select expression.
2993        """
2994        return _apply_builder(
2995            expression=expression,
2996            instance=self,
2997            arg="offset",
2998            into=Offset,
2999            prefix="OFFSET",
3000            dialect=dialect,
3001            copy=copy,
3002            into_arg="expression",
3003            **opts,
3004        )
3005
3006    def select(
3007        self,
3008        *expressions: t.Optional[ExpOrStr],
3009        append: bool = True,
3010        dialect: DialectType = None,
3011        copy: bool = True,
3012        **opts,
3013    ) -> Select:
3014        """
3015        Append to or set the SELECT expressions.
3016
3017        Example:
3018            >>> Select().select("x", "y").sql()
3019            'SELECT x, y'
3020
3021        Args:
3022            *expressions: the SQL code strings to parse.
3023                If an `Expression` instance is passed, it will be used as-is.
3024            append: if `True`, add to any existing expressions.
3025                Otherwise, this resets the expressions.
3026            dialect: the dialect used to parse the input expressions.
3027            copy: if `False`, modify this expression instance in-place.
3028            opts: other options to use to parse the input expressions.
3029
3030        Returns:
3031            The modified Select expression.
3032        """
3033        return _apply_list_builder(
3034            *expressions,
3035            instance=self,
3036            arg="expressions",
3037            append=append,
3038            dialect=dialect,
3039            copy=copy,
3040            **opts,
3041        )
3042
3043    def lateral(
3044        self,
3045        *expressions: t.Optional[ExpOrStr],
3046        append: bool = True,
3047        dialect: DialectType = None,
3048        copy: bool = True,
3049        **opts,
3050    ) -> Select:
3051        """
3052        Append to or set the LATERAL expressions.
3053
3054        Example:
3055            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3056            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3057
3058        Args:
3059            *expressions: the SQL code strings to parse.
3060                If an `Expression` instance is passed, it will be used as-is.
3061            append: if `True`, add to any existing expressions.
3062                Otherwise, this resets the expressions.
3063            dialect: the dialect used to parse the input expressions.
3064            copy: if `False`, modify this expression instance in-place.
3065            opts: other options to use to parse the input expressions.
3066
3067        Returns:
3068            The modified Select expression.
3069        """
3070        return _apply_list_builder(
3071            *expressions,
3072            instance=self,
3073            arg="laterals",
3074            append=append,
3075            into=Lateral,
3076            prefix="LATERAL VIEW",
3077            dialect=dialect,
3078            copy=copy,
3079            **opts,
3080        )
3081
3082    def join(
3083        self,
3084        expression: ExpOrStr,
3085        on: t.Optional[ExpOrStr] = None,
3086        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3087        append: bool = True,
3088        join_type: t.Optional[str] = None,
3089        join_alias: t.Optional[Identifier | str] = None,
3090        dialect: DialectType = None,
3091        copy: bool = True,
3092        **opts,
3093    ) -> Select:
3094        """
3095        Append to or set the JOIN expressions.
3096
3097        Example:
3098            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3099            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3100
3101            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3102            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3103
3104            Use `join_type` to change the type of join:
3105
3106            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3107            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3108
3109        Args:
3110            expression: the SQL code string to parse.
3111                If an `Expression` instance is passed, it will be used as-is.
3112            on: optionally specify the join "on" criteria as a SQL string.
3113                If an `Expression` instance is passed, it will be used as-is.
3114            using: optionally specify the join "using" criteria as a SQL string.
3115                If an `Expression` instance is passed, it will be used as-is.
3116            append: if `True`, add to any existing expressions.
3117                Otherwise, this resets the expressions.
3118            join_type: if set, alter the parsed join type.
3119            join_alias: an optional alias for the joined source.
3120            dialect: the dialect used to parse the input expressions.
3121            copy: if `False`, modify this expression instance in-place.
3122            opts: other options to use to parse the input expressions.
3123
3124        Returns:
3125            Select: the modified expression.
3126        """
3127        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3128
3129        try:
3130            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3131        except ParseError:
3132            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3133
3134        join = expression if isinstance(expression, Join) else Join(this=expression)
3135
3136        if isinstance(join.this, Select):
3137            join.this.replace(join.this.subquery())
3138
3139        if join_type:
3140            method: t.Optional[Token]
3141            side: t.Optional[Token]
3142            kind: t.Optional[Token]
3143
3144            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3145
3146            if method:
3147                join.set("method", method.text)
3148            if side:
3149                join.set("side", side.text)
3150            if kind:
3151                join.set("kind", kind.text)
3152
3153        if on:
3154            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3155            join.set("on", on)
3156
3157        if using:
3158            join = _apply_list_builder(
3159                *ensure_list(using),
3160                instance=join,
3161                arg="using",
3162                append=append,
3163                copy=copy,
3164                into=Identifier,
3165                **opts,
3166            )
3167
3168        if join_alias:
3169            join.set("this", alias_(join.this, join_alias, table=True))
3170
3171        return _apply_list_builder(
3172            join,
3173            instance=self,
3174            arg="joins",
3175            append=append,
3176            copy=copy,
3177            **opts,
3178        )
3179
3180    def where(
3181        self,
3182        *expressions: t.Optional[ExpOrStr],
3183        append: bool = True,
3184        dialect: DialectType = None,
3185        copy: bool = True,
3186        **opts,
3187    ) -> Select:
3188        """
3189        Append to or set the WHERE expressions.
3190
3191        Example:
3192            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3193            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3194
3195        Args:
3196            *expressions: the SQL code strings to parse.
3197                If an `Expression` instance is passed, it will be used as-is.
3198                Multiple expressions are combined with an AND operator.
3199            append: if `True`, AND the new expressions to any existing expression.
3200                Otherwise, this resets the expression.
3201            dialect: the dialect used to parse the input expressions.
3202            copy: if `False`, modify this expression instance in-place.
3203            opts: other options to use to parse the input expressions.
3204
3205        Returns:
3206            Select: the modified expression.
3207        """
3208        return _apply_conjunction_builder(
3209            *expressions,
3210            instance=self,
3211            arg="where",
3212            append=append,
3213            into=Where,
3214            dialect=dialect,
3215            copy=copy,
3216            **opts,
3217        )
3218
3219    def having(
3220        self,
3221        *expressions: t.Optional[ExpOrStr],
3222        append: bool = True,
3223        dialect: DialectType = None,
3224        copy: bool = True,
3225        **opts,
3226    ) -> Select:
3227        """
3228        Append to or set the HAVING expressions.
3229
3230        Example:
3231            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3232            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3233
3234        Args:
3235            *expressions: the SQL code strings to parse.
3236                If an `Expression` instance is passed, it will be used as-is.
3237                Multiple expressions are combined with an AND operator.
3238            append: if `True`, AND the new expressions to any existing expression.
3239                Otherwise, this resets the expression.
3240            dialect: the dialect used to parse the input expressions.
3241            copy: if `False`, modify this expression instance in-place.
3242            opts: other options to use to parse the input expressions.
3243
3244        Returns:
3245            The modified Select expression.
3246        """
3247        return _apply_conjunction_builder(
3248            *expressions,
3249            instance=self,
3250            arg="having",
3251            append=append,
3252            into=Having,
3253            dialect=dialect,
3254            copy=copy,
3255            **opts,
3256        )
3257
3258    def window(
3259        self,
3260        *expressions: t.Optional[ExpOrStr],
3261        append: bool = True,
3262        dialect: DialectType = None,
3263        copy: bool = True,
3264        **opts,
3265    ) -> Select:
3266        return _apply_list_builder(
3267            *expressions,
3268            instance=self,
3269            arg="windows",
3270            append=append,
3271            into=Window,
3272            dialect=dialect,
3273            copy=copy,
3274            **opts,
3275        )
3276
3277    def qualify(
3278        self,
3279        *expressions: t.Optional[ExpOrStr],
3280        append: bool = True,
3281        dialect: DialectType = None,
3282        copy: bool = True,
3283        **opts,
3284    ) -> Select:
3285        return _apply_conjunction_builder(
3286            *expressions,
3287            instance=self,
3288            arg="qualify",
3289            append=append,
3290            into=Qualify,
3291            dialect=dialect,
3292            copy=copy,
3293            **opts,
3294        )
3295
3296    def distinct(
3297        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3298    ) -> Select:
3299        """
3300        Set the OFFSET expression.
3301
3302        Example:
3303            >>> Select().from_("tbl").select("x").distinct().sql()
3304            'SELECT DISTINCT x FROM tbl'
3305
3306        Args:
3307            ons: the expressions to distinct on
3308            distinct: whether the Select should be distinct
3309            copy: if `False`, modify this expression instance in-place.
3310
3311        Returns:
3312            Select: the modified expression.
3313        """
3314        instance = maybe_copy(self, copy)
3315        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3316        instance.set("distinct", Distinct(on=on) if distinct else None)
3317        return instance
3318
3319    def ctas(
3320        self,
3321        table: ExpOrStr,
3322        properties: t.Optional[t.Dict] = None,
3323        dialect: DialectType = None,
3324        copy: bool = True,
3325        **opts,
3326    ) -> Create:
3327        """
3328        Convert this expression to a CREATE TABLE AS statement.
3329
3330        Example:
3331            >>> Select().select("*").from_("tbl").ctas("x").sql()
3332            'CREATE TABLE x AS SELECT * FROM tbl'
3333
3334        Args:
3335            table: the SQL code string to parse as the table name.
3336                If another `Expression` instance is passed, it will be used as-is.
3337            properties: an optional mapping of table properties
3338            dialect: the dialect used to parse the input table.
3339            copy: if `False`, modify this expression instance in-place.
3340            opts: other options to use to parse the input table.
3341
3342        Returns:
3343            The new Create expression.
3344        """
3345        instance = maybe_copy(self, copy)
3346        table_expression = maybe_parse(
3347            table,
3348            into=Table,
3349            dialect=dialect,
3350            **opts,
3351        )
3352        properties_expression = None
3353        if properties:
3354            properties_expression = Properties.from_dict(properties)
3355
3356        return Create(
3357            this=table_expression,
3358            kind="table",
3359            expression=instance,
3360            properties=properties_expression,
3361        )
3362
3363    def lock(self, update: bool = True, copy: bool = True) -> Select:
3364        """
3365        Set the locking read mode for this expression.
3366
3367        Examples:
3368            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3369            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3370
3371            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3372            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3373
3374        Args:
3375            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3376            copy: if `False`, modify this expression instance in-place.
3377
3378        Returns:
3379            The modified expression.
3380        """
3381        inst = maybe_copy(self, copy)
3382        inst.set("locks", [Lock(update=update)])
3383
3384        return inst
3385
3386    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3387        """
3388        Set hints for this expression.
3389
3390        Examples:
3391            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3392            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3393
3394        Args:
3395            hints: The SQL code strings to parse as the hints.
3396                If an `Expression` instance is passed, it will be used as-is.
3397            dialect: The dialect used to parse the hints.
3398            copy: If `False`, modify this expression instance in-place.
3399
3400        Returns:
3401            The modified expression.
3402        """
3403        inst = maybe_copy(self, copy)
3404        inst.set(
3405            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3406        )
3407
3408        return inst
3409
3410    @property
3411    def named_selects(self) -> t.List[str]:
3412        return [e.output_name for e in self.expressions if e.alias_or_name]
3413
3414    @property
3415    def is_star(self) -> bool:
3416        return any(expression.is_star for expression in self.expressions)
3417
3418    @property
3419    def selects(self) -> t.List[Expression]:
3420        return self.expressions
3421
3422
3423class Subquery(DerivedTable, Unionable):
3424    arg_types = {
3425        "this": True,
3426        "alias": False,
3427        "with": False,
3428        **QUERY_MODIFIERS,
3429    }
3430
3431    def unnest(self):
3432        """
3433        Returns the first non subquery.
3434        """
3435        expression = self
3436        while isinstance(expression, Subquery):
3437            expression = expression.this
3438        return expression
3439
3440    def unwrap(self) -> Subquery:
3441        expression = self
3442        while expression.same_parent and expression.is_wrapper:
3443            expression = t.cast(Subquery, expression.parent)
3444        return expression
3445
3446    @property
3447    def is_wrapper(self) -> bool:
3448        """
3449        Whether this Subquery acts as a simple wrapper around another expression.
3450
3451        SELECT * FROM (((SELECT * FROM t)))
3452                      ^
3453                      This corresponds to a "wrapper" Subquery node
3454        """
3455        return all(v is None for k, v in self.args.items() if k != "this")
3456
3457    @property
3458    def is_star(self) -> bool:
3459        return self.this.is_star
3460
3461    @property
3462    def output_name(self) -> str:
3463        return self.alias
3464
3465
3466class TableSample(Expression):
3467    arg_types = {
3468        "this": False,
3469        "expressions": False,
3470        "method": False,
3471        "bucket_numerator": False,
3472        "bucket_denominator": False,
3473        "bucket_field": False,
3474        "percent": False,
3475        "rows": False,
3476        "size": False,
3477        "seed": False,
3478        "kind": False,
3479    }
3480
3481
3482class Tag(Expression):
3483    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3484
3485    arg_types = {
3486        "this": False,
3487        "prefix": False,
3488        "postfix": False,
3489    }
3490
3491
3492# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3493# https://duckdb.org/docs/sql/statements/pivot
3494class Pivot(Expression):
3495    arg_types = {
3496        "this": False,
3497        "alias": False,
3498        "expressions": False,
3499        "field": False,
3500        "unpivot": False,
3501        "using": False,
3502        "group": False,
3503        "columns": False,
3504        "include_nulls": False,
3505    }
3506
3507
3508class Window(Condition):
3509    arg_types = {
3510        "this": True,
3511        "partition_by": False,
3512        "order": False,
3513        "spec": False,
3514        "alias": False,
3515        "over": False,
3516        "first": False,
3517    }
3518
3519
3520class WindowSpec(Expression):
3521    arg_types = {
3522        "kind": False,
3523        "start": False,
3524        "start_side": False,
3525        "end": False,
3526        "end_side": False,
3527    }
3528
3529
3530class Where(Expression):
3531    pass
3532
3533
3534class Star(Expression):
3535    arg_types = {"except": False, "replace": False}
3536
3537    @property
3538    def name(self) -> str:
3539        return "*"
3540
3541    @property
3542    def output_name(self) -> str:
3543        return self.name
3544
3545
3546class Parameter(Condition):
3547    arg_types = {"this": True, "expression": False}
3548
3549
3550class SessionParameter(Condition):
3551    arg_types = {"this": True, "kind": False}
3552
3553
3554class Placeholder(Condition):
3555    arg_types = {"this": False, "kind": False}
3556
3557
3558class Null(Condition):
3559    arg_types: t.Dict[str, t.Any] = {}
3560
3561    @property
3562    def name(self) -> str:
3563        return "NULL"
3564
3565
3566class Boolean(Condition):
3567    pass
3568
3569
3570class DataTypeParam(Expression):
3571    arg_types = {"this": True, "expression": False}
3572
3573
3574class DataType(Expression):
3575    arg_types = {
3576        "this": True,
3577        "expressions": False,
3578        "nested": False,
3579        "values": False,
3580        "prefix": False,
3581        "kind": False,
3582    }
3583
3584    class Type(AutoName):
3585        ARRAY = auto()
3586        BIGDECIMAL = auto()
3587        BIGINT = auto()
3588        BIGSERIAL = auto()
3589        BINARY = auto()
3590        BIT = auto()
3591        BOOLEAN = auto()
3592        CHAR = auto()
3593        DATE = auto()
3594        DATEMULTIRANGE = auto()
3595        DATERANGE = auto()
3596        DATETIME = auto()
3597        DATETIME64 = auto()
3598        DECIMAL = auto()
3599        DOUBLE = auto()
3600        ENUM = auto()
3601        ENUM8 = auto()
3602        ENUM16 = auto()
3603        FIXEDSTRING = auto()
3604        FLOAT = auto()
3605        GEOGRAPHY = auto()
3606        GEOMETRY = auto()
3607        HLLSKETCH = auto()
3608        HSTORE = auto()
3609        IMAGE = auto()
3610        INET = auto()
3611        INT = auto()
3612        INT128 = auto()
3613        INT256 = auto()
3614        INT4MULTIRANGE = auto()
3615        INT4RANGE = auto()
3616        INT8MULTIRANGE = auto()
3617        INT8RANGE = auto()
3618        INTERVAL = auto()
3619        IPADDRESS = auto()
3620        IPPREFIX = auto()
3621        JSON = auto()
3622        JSONB = auto()
3623        LONGBLOB = auto()
3624        LONGTEXT = auto()
3625        LOWCARDINALITY = auto()
3626        MAP = auto()
3627        MEDIUMBLOB = auto()
3628        MEDIUMINT = auto()
3629        MEDIUMTEXT = auto()
3630        MONEY = auto()
3631        NCHAR = auto()
3632        NESTED = auto()
3633        NULL = auto()
3634        NULLABLE = auto()
3635        NUMMULTIRANGE = auto()
3636        NUMRANGE = auto()
3637        NVARCHAR = auto()
3638        OBJECT = auto()
3639        ROWVERSION = auto()
3640        SERIAL = auto()
3641        SET = auto()
3642        SMALLINT = auto()
3643        SMALLMONEY = auto()
3644        SMALLSERIAL = auto()
3645        STRUCT = auto()
3646        SUPER = auto()
3647        TEXT = auto()
3648        TINYBLOB = auto()
3649        TINYTEXT = auto()
3650        TIME = auto()
3651        TIMETZ = auto()
3652        TIMESTAMP = auto()
3653        TIMESTAMPLTZ = auto()
3654        TIMESTAMPTZ = auto()
3655        TIMESTAMP_S = auto()
3656        TIMESTAMP_MS = auto()
3657        TIMESTAMP_NS = auto()
3658        TINYINT = auto()
3659        TSMULTIRANGE = auto()
3660        TSRANGE = auto()
3661        TSTZMULTIRANGE = auto()
3662        TSTZRANGE = auto()
3663        UBIGINT = auto()
3664        UINT = auto()
3665        UINT128 = auto()
3666        UINT256 = auto()
3667        UMEDIUMINT = auto()
3668        UDECIMAL = auto()
3669        UNIQUEIDENTIFIER = auto()
3670        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3671        USERDEFINED = "USER-DEFINED"
3672        USMALLINT = auto()
3673        UTINYINT = auto()
3674        UUID = auto()
3675        VARBINARY = auto()
3676        VARCHAR = auto()
3677        VARIANT = auto()
3678        XML = auto()
3679        YEAR = auto()
3680
3681    TEXT_TYPES = {
3682        Type.CHAR,
3683        Type.NCHAR,
3684        Type.VARCHAR,
3685        Type.NVARCHAR,
3686        Type.TEXT,
3687    }
3688
3689    INTEGER_TYPES = {
3690        Type.INT,
3691        Type.TINYINT,
3692        Type.SMALLINT,
3693        Type.BIGINT,
3694        Type.INT128,
3695        Type.INT256,
3696        Type.BIT,
3697    }
3698
3699    FLOAT_TYPES = {
3700        Type.FLOAT,
3701        Type.DOUBLE,
3702    }
3703
3704    NUMERIC_TYPES = {
3705        *INTEGER_TYPES,
3706        *FLOAT_TYPES,
3707    }
3708
3709    TEMPORAL_TYPES = {
3710        Type.TIME,
3711        Type.TIMETZ,
3712        Type.TIMESTAMP,
3713        Type.TIMESTAMPTZ,
3714        Type.TIMESTAMPLTZ,
3715        Type.TIMESTAMP_S,
3716        Type.TIMESTAMP_MS,
3717        Type.TIMESTAMP_NS,
3718        Type.DATE,
3719        Type.DATETIME,
3720        Type.DATETIME64,
3721    }
3722
3723    @classmethod
3724    def build(
3725        cls,
3726        dtype: DATA_TYPE,
3727        dialect: DialectType = None,
3728        udt: bool = False,
3729        **kwargs,
3730    ) -> DataType:
3731        """
3732        Constructs a DataType object.
3733
3734        Args:
3735            dtype: the data type of interest.
3736            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3737            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3738                DataType, thus creating a user-defined type.
3739            kawrgs: additional arguments to pass in the constructor of DataType.
3740
3741        Returns:
3742            The constructed DataType object.
3743        """
3744        from sqlglot import parse_one
3745
3746        if isinstance(dtype, str):
3747            if dtype.upper() == "UNKNOWN":
3748                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3749
3750            try:
3751                data_type_exp = parse_one(
3752                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3753                )
3754            except ParseError:
3755                if udt:
3756                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3757                raise
3758        elif isinstance(dtype, DataType.Type):
3759            data_type_exp = DataType(this=dtype)
3760        elif isinstance(dtype, DataType):
3761            return dtype
3762        else:
3763            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3764
3765        return DataType(**{**data_type_exp.args, **kwargs})
3766
3767    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3768        """
3769        Checks whether this DataType matches one of the provided data types. Nested types or precision
3770        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3771
3772        Args:
3773            dtypes: the data types to compare this DataType to.
3774
3775        Returns:
3776            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3777        """
3778        for dtype in dtypes:
3779            other = DataType.build(dtype, udt=True)
3780
3781            if (
3782                other.expressions
3783                or self.this == DataType.Type.USERDEFINED
3784                or other.this == DataType.Type.USERDEFINED
3785            ):
3786                matches = self == other
3787            else:
3788                matches = self.this == other.this
3789
3790            if matches:
3791                return True
3792        return False
3793
3794
3795DATA_TYPE = t.Union[str, DataType, DataType.Type]
3796
3797
3798# https://www.postgresql.org/docs/15/datatype-pseudo.html
3799class PseudoType(DataType):
3800    arg_types = {"this": True}
3801
3802
3803# https://www.postgresql.org/docs/15/datatype-oid.html
3804class ObjectIdentifier(DataType):
3805    arg_types = {"this": True}
3806
3807
3808# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3809class SubqueryPredicate(Predicate):
3810    pass
3811
3812
3813class All(SubqueryPredicate):
3814    pass
3815
3816
3817class Any(SubqueryPredicate):
3818    pass
3819
3820
3821class Exists(SubqueryPredicate):
3822    pass
3823
3824
3825# Commands to interact with the databases or engines. For most of the command
3826# expressions we parse whatever comes after the command's name as a string.
3827class Command(Expression):
3828    arg_types = {"this": True, "expression": False}
3829
3830
3831class Transaction(Expression):
3832    arg_types = {"this": False, "modes": False, "mark": False}
3833
3834
3835class Commit(Expression):
3836    arg_types = {"chain": False, "this": False, "durability": False}
3837
3838
3839class Rollback(Expression):
3840    arg_types = {"savepoint": False, "this": False}
3841
3842
3843class AlterTable(Expression):
3844    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3845
3846
3847class AddConstraint(Expression):
3848    arg_types = {"this": False, "expression": False, "enforced": False}
3849
3850
3851class DropPartition(Expression):
3852    arg_types = {"expressions": True, "exists": False}
3853
3854
3855# Binary expressions like (ADD a b)
3856class Binary(Condition):
3857    arg_types = {"this": True, "expression": True}
3858
3859    @property
3860    def left(self) -> Expression:
3861        return self.this
3862
3863    @property
3864    def right(self) -> Expression:
3865        return self.expression
3866
3867
3868class Add(Binary):
3869    pass
3870
3871
3872class Connector(Binary):
3873    pass
3874
3875
3876class And(Connector):
3877    pass
3878
3879
3880class Or(Connector):
3881    pass
3882
3883
3884class BitwiseAnd(Binary):
3885    pass
3886
3887
3888class BitwiseLeftShift(Binary):
3889    pass
3890
3891
3892class BitwiseOr(Binary):
3893    pass
3894
3895
3896class BitwiseRightShift(Binary):
3897    pass
3898
3899
3900class BitwiseXor(Binary):
3901    pass
3902
3903
3904class Div(Binary):
3905    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3906
3907
3908class Overlaps(Binary):
3909    pass
3910
3911
3912class Dot(Binary):
3913    @property
3914    def name(self) -> str:
3915        return self.expression.name
3916
3917    @property
3918    def output_name(self) -> str:
3919        return self.name
3920
3921    @classmethod
3922    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3923        """Build a Dot object with a sequence of expressions."""
3924        if len(expressions) < 2:
3925            raise ValueError(f"Dot requires >= 2 expressions.")
3926
3927        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3928
3929    @property
3930    def parts(self) -> t.List[Expression]:
3931        """Return the parts of a table / column in order catalog, db, table."""
3932        this, *parts = self.flatten()
3933
3934        parts.reverse()
3935
3936        for arg in ("this", "table", "db", "catalog"):
3937            part = this.args.get(arg)
3938
3939            if isinstance(part, Expression):
3940                parts.append(part)
3941
3942        parts.reverse()
3943        return parts
3944
3945
3946class DPipe(Binary):
3947    pass
3948
3949
3950class SafeDPipe(DPipe):
3951    pass
3952
3953
3954class EQ(Binary, Predicate):
3955    pass
3956
3957
3958class NullSafeEQ(Binary, Predicate):
3959    pass
3960
3961
3962class NullSafeNEQ(Binary, Predicate):
3963    pass
3964
3965
3966# Represents e.g. := in DuckDB which is mostly used for setting parameters
3967class PropertyEQ(Binary):
3968    pass
3969
3970
3971class Distance(Binary):
3972    pass
3973
3974
3975class Escape(Binary):
3976    pass
3977
3978
3979class Glob(Binary, Predicate):
3980    pass
3981
3982
3983class GT(Binary, Predicate):
3984    pass
3985
3986
3987class GTE(Binary, Predicate):
3988    pass
3989
3990
3991class ILike(Binary, Predicate):
3992    pass
3993
3994
3995class ILikeAny(Binary, Predicate):
3996    pass
3997
3998
3999class IntDiv(Binary):
4000    pass
4001
4002
4003class Is(Binary, Predicate):
4004    pass
4005
4006
4007class Kwarg(Binary):
4008    """Kwarg in special functions like func(kwarg => y)."""
4009
4010
4011class Like(Binary, Predicate):
4012    pass
4013
4014
4015class LikeAny(Binary, Predicate):
4016    pass
4017
4018
4019class LT(Binary, Predicate):
4020    pass
4021
4022
4023class LTE(Binary, Predicate):
4024    pass
4025
4026
4027class Mod(Binary):
4028    pass
4029
4030
4031class Mul(Binary):
4032    pass
4033
4034
4035class NEQ(Binary, Predicate):
4036    pass
4037
4038
4039# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4040class Operator(Binary):
4041    arg_types = {"this": True, "operator": True, "expression": True}
4042
4043
4044class SimilarTo(Binary, Predicate):
4045    pass
4046
4047
4048class Slice(Binary):
4049    arg_types = {"this": False, "expression": False}
4050
4051
4052class Sub(Binary):
4053    pass
4054
4055
4056class ArrayOverlaps(Binary):
4057    pass
4058
4059
4060# Unary Expressions
4061# (NOT a)
4062class Unary(Condition):
4063    pass
4064
4065
4066class BitwiseNot(Unary):
4067    pass
4068
4069
4070class Not(Unary):
4071    pass
4072
4073
4074class Paren(Unary):
4075    arg_types = {"this": True, "with": False}
4076
4077    @property
4078    def output_name(self) -> str:
4079        return self.this.name
4080
4081
4082class Neg(Unary):
4083    pass
4084
4085
4086class Alias(Expression):
4087    arg_types = {"this": True, "alias": False}
4088
4089    @property
4090    def output_name(self) -> str:
4091        return self.alias
4092
4093
4094class Aliases(Expression):
4095    arg_types = {"this": True, "expressions": True}
4096
4097    @property
4098    def aliases(self):
4099        return self.expressions
4100
4101
4102class AtTimeZone(Expression):
4103    arg_types = {"this": True, "zone": True}
4104
4105
4106class Between(Predicate):
4107    arg_types = {"this": True, "low": True, "high": True}
4108
4109
4110class Bracket(Condition):
4111    arg_types = {"this": True, "expressions": True}
4112
4113    @property
4114    def output_name(self) -> str:
4115        if len(self.expressions) == 1:
4116            return self.expressions[0].output_name
4117
4118        return super().output_name
4119
4120
4121class SafeBracket(Bracket):
4122    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4123
4124
4125class Distinct(Expression):
4126    arg_types = {"expressions": False, "on": False}
4127
4128
4129class In(Predicate):
4130    arg_types = {
4131        "this": True,
4132        "expressions": False,
4133        "query": False,
4134        "unnest": False,
4135        "field": False,
4136        "is_global": False,
4137    }
4138
4139
4140# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4141class ForIn(Expression):
4142    arg_types = {"this": True, "expression": True}
4143
4144
4145class TimeUnit(Expression):
4146    """Automatically converts unit arg into a var."""
4147
4148    arg_types = {"unit": False}
4149
4150    UNABBREVIATED_UNIT_NAME = {
4151        "d": "day",
4152        "h": "hour",
4153        "m": "minute",
4154        "ms": "millisecond",
4155        "ns": "nanosecond",
4156        "q": "quarter",
4157        "s": "second",
4158        "us": "microsecond",
4159        "w": "week",
4160        "y": "year",
4161    }
4162
4163    VAR_LIKE = (Column, Literal, Var)
4164
4165    def __init__(self, **args):
4166        unit = args.get("unit")
4167        if isinstance(unit, self.VAR_LIKE):
4168            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4169        elif isinstance(unit, Week):
4170            unit.set("this", Var(this=unit.this.name))
4171
4172        super().__init__(**args)
4173
4174    @property
4175    def unit(self) -> t.Optional[Var]:
4176        return self.args.get("unit")
4177
4178
4179class IntervalOp(TimeUnit):
4180    arg_types = {"unit": True, "expression": True}
4181
4182    def interval(self):
4183        return Interval(
4184            this=self.expression.copy(),
4185            unit=self.unit.copy(),
4186        )
4187
4188
4189# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4190# https://trino.io/docs/current/language/types.html#interval-day-to-second
4191# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4192class IntervalSpan(DataType):
4193    arg_types = {"this": True, "expression": True}
4194
4195
4196class Interval(TimeUnit):
4197    arg_types = {"this": False, "unit": False}
4198
4199
4200class IgnoreNulls(Expression):
4201    pass
4202
4203
4204class RespectNulls(Expression):
4205    pass
4206
4207
4208# Functions
4209class Func(Condition):
4210    """
4211    The base class for all function expressions.
4212
4213    Attributes:
4214        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4215            treated as a variable length argument and the argument's value will be stored as a list.
4216        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4217            for this function expression. These values are used to map this node to a name during parsing
4218            as well as to provide the function's name during SQL string generation. By default the SQL
4219            name is set to the expression's class name transformed to snake case.
4220    """
4221
4222    is_var_len_args = False
4223
4224    @classmethod
4225    def from_arg_list(cls, args):
4226        if cls.is_var_len_args:
4227            all_arg_keys = list(cls.arg_types)
4228            # If this function supports variable length argument treat the last argument as such.
4229            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4230            num_non_var = len(non_var_len_arg_keys)
4231
4232            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4233            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4234        else:
4235            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4236
4237        return cls(**args_dict)
4238
4239    @classmethod
4240    def sql_names(cls):
4241        if cls is Func:
4242            raise NotImplementedError(
4243                "SQL name is only supported by concrete function implementations"
4244            )
4245        if "_sql_names" not in cls.__dict__:
4246            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4247        return cls._sql_names
4248
4249    @classmethod
4250    def sql_name(cls):
4251        return cls.sql_names()[0]
4252
4253    @classmethod
4254    def default_parser_mappings(cls):
4255        return {name: cls.from_arg_list for name in cls.sql_names()}
4256
4257
4258class AggFunc(Func):
4259    pass
4260
4261
4262class ParameterizedAgg(AggFunc):
4263    arg_types = {"this": True, "expressions": True, "params": True}
4264
4265
4266class Abs(Func):
4267    pass
4268
4269
4270class ArgMax(AggFunc):
4271    arg_types = {"this": True, "expression": True, "count": False}
4272    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4273
4274
4275class ArgMin(AggFunc):
4276    arg_types = {"this": True, "expression": True, "count": False}
4277    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4278
4279
4280class ApproxTopK(AggFunc):
4281    arg_types = {"this": True, "expression": False, "counters": False}
4282
4283
4284class Flatten(Func):
4285    pass
4286
4287
4288# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4289class Transform(Func):
4290    arg_types = {"this": True, "expression": True}
4291
4292
4293class Anonymous(Func):
4294    arg_types = {"this": True, "expressions": False}
4295    is_var_len_args = True
4296
4297
4298# https://docs.snowflake.com/en/sql-reference/functions/hll
4299# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4300class Hll(AggFunc):
4301    arg_types = {"this": True, "expressions": False}
4302    is_var_len_args = True
4303
4304
4305class ApproxDistinct(AggFunc):
4306    arg_types = {"this": True, "accuracy": False}
4307    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4308
4309
4310class Array(Func):
4311    arg_types = {"expressions": False}
4312    is_var_len_args = True
4313
4314
4315# https://docs.snowflake.com/en/sql-reference/functions/to_char
4316# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4317class ToChar(Func):
4318    arg_types = {"this": True, "format": False, "nlsparam": False}
4319
4320
4321class GenerateSeries(Func):
4322    arg_types = {"start": True, "end": True, "step": False}
4323
4324
4325class ArrayAgg(AggFunc):
4326    pass
4327
4328
4329class ArrayUniqueAgg(AggFunc):
4330    pass
4331
4332
4333class ArrayAll(Func):
4334    arg_types = {"this": True, "expression": True}
4335
4336
4337class ArrayAny(Func):
4338    arg_types = {"this": True, "expression": True}
4339
4340
4341class ArrayConcat(Func):
4342    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4343    arg_types = {"this": True, "expressions": False}
4344    is_var_len_args = True
4345
4346
4347class ArrayContains(Binary, Func):
4348    pass
4349
4350
4351class ArrayContained(Binary):
4352    pass
4353
4354
4355class ArrayFilter(Func):
4356    arg_types = {"this": True, "expression": True}
4357    _sql_names = ["FILTER", "ARRAY_FILTER"]
4358
4359
4360class ArrayJoin(Func):
4361    arg_types = {"this": True, "expression": True, "null": False}
4362
4363
4364class ArraySize(Func):
4365    arg_types = {"this": True, "expression": False}
4366
4367
4368class ArraySort(Func):
4369    arg_types = {"this": True, "expression": False}
4370
4371
4372class ArraySum(Func):
4373    pass
4374
4375
4376class ArrayUnionAgg(AggFunc):
4377    pass
4378
4379
4380class Avg(AggFunc):
4381    pass
4382
4383
4384class AnyValue(AggFunc):
4385    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4386
4387
4388class First(Func):
4389    arg_types = {"this": True, "ignore_nulls": False}
4390
4391
4392class Last(Func):
4393    arg_types = {"this": True, "ignore_nulls": False}
4394
4395
4396class Case(Func):
4397    arg_types = {"this": False, "ifs": True, "default": False}
4398
4399    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4400        instance = maybe_copy(self, copy)
4401        instance.append(
4402            "ifs",
4403            If(
4404                this=maybe_parse(condition, copy=copy, **opts),
4405                true=maybe_parse(then, copy=copy, **opts),
4406            ),
4407        )
4408        return instance
4409
4410    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4411        instance = maybe_copy(self, copy)
4412        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4413        return instance
4414
4415
4416class Cast(Func):
4417    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4418
4419    @property
4420    def name(self) -> str:
4421        return self.this.name
4422
4423    @property
4424    def to(self) -> DataType:
4425        return self.args["to"]
4426
4427    @property
4428    def output_name(self) -> str:
4429        return self.name
4430
4431    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4432        """
4433        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4434        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4435        array<int> != array<float>.
4436
4437        Args:
4438            dtypes: the data types to compare this Cast's DataType to.
4439
4440        Returns:
4441            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4442        """
4443        return self.to.is_type(*dtypes)
4444
4445
4446class TryCast(Cast):
4447    pass
4448
4449
4450class CastToStrType(Func):
4451    arg_types = {"this": True, "to": True}
4452
4453
4454class Collate(Binary, Func):
4455    pass
4456
4457
4458class Ceil(Func):
4459    arg_types = {"this": True, "decimals": False}
4460    _sql_names = ["CEIL", "CEILING"]
4461
4462
4463class Coalesce(Func):
4464    arg_types = {"this": True, "expressions": False}
4465    is_var_len_args = True
4466    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4467
4468
4469class Chr(Func):
4470    arg_types = {"this": True, "charset": False, "expressions": False}
4471    is_var_len_args = True
4472    _sql_names = ["CHR", "CHAR"]
4473
4474
4475class Concat(Func):
4476    arg_types = {"expressions": True}
4477    is_var_len_args = True
4478
4479
4480class SafeConcat(Concat):
4481    pass
4482
4483
4484class ConcatWs(Concat):
4485    _sql_names = ["CONCAT_WS"]
4486
4487
4488class Count(AggFunc):
4489    arg_types = {"this": False, "expressions": False}
4490    is_var_len_args = True
4491
4492
4493class CountIf(AggFunc):
4494    pass
4495
4496
4497class CurrentDate(Func):
4498    arg_types = {"this": False}
4499
4500
4501class CurrentDatetime(Func):
4502    arg_types = {"this": False}
4503
4504
4505class CurrentTime(Func):
4506    arg_types = {"this": False}
4507
4508
4509class CurrentTimestamp(Func):
4510    arg_types = {"this": False}
4511
4512
4513class CurrentUser(Func):
4514    arg_types = {"this": False}
4515
4516
4517class DateAdd(Func, IntervalOp):
4518    arg_types = {"this": True, "expression": True, "unit": False}
4519
4520
4521class DateSub(Func, IntervalOp):
4522    arg_types = {"this": True, "expression": True, "unit": False}
4523
4524
4525class DateDiff(Func, TimeUnit):
4526    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4527    arg_types = {"this": True, "expression": True, "unit": False}
4528
4529
4530class DateTrunc(Func):
4531    arg_types = {"unit": True, "this": True, "zone": False}
4532
4533    @property
4534    def unit(self) -> Expression:
4535        return self.args["unit"]
4536
4537
4538class DatetimeAdd(Func, IntervalOp):
4539    arg_types = {"this": True, "expression": True, "unit": False}
4540
4541
4542class DatetimeSub(Func, IntervalOp):
4543    arg_types = {"this": True, "expression": True, "unit": False}
4544
4545
4546class DatetimeDiff(Func, TimeUnit):
4547    arg_types = {"this": True, "expression": True, "unit": False}
4548
4549
4550class DatetimeTrunc(Func, TimeUnit):
4551    arg_types = {"this": True, "unit": True, "zone": False}
4552
4553
4554class DayOfWeek(Func):
4555    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4556
4557
4558class DayOfMonth(Func):
4559    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4560
4561
4562class DayOfYear(Func):
4563    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4564
4565
4566class ToDays(Func):
4567    pass
4568
4569
4570class WeekOfYear(Func):
4571    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4572
4573
4574class MonthsBetween(Func):
4575    arg_types = {"this": True, "expression": True, "roundoff": False}
4576
4577
4578class LastDateOfMonth(Func):
4579    pass
4580
4581
4582class Extract(Func):
4583    arg_types = {"this": True, "expression": True}
4584
4585
4586class Timestamp(Func):
4587    arg_types = {"this": False, "expression": False}
4588
4589
4590class TimestampAdd(Func, TimeUnit):
4591    arg_types = {"this": True, "expression": True, "unit": False}
4592
4593
4594class TimestampSub(Func, TimeUnit):
4595    arg_types = {"this": True, "expression": True, "unit": False}
4596
4597
4598class TimestampDiff(Func, TimeUnit):
4599    arg_types = {"this": True, "expression": True, "unit": False}
4600
4601
4602class TimestampTrunc(Func, TimeUnit):
4603    arg_types = {"this": True, "unit": True, "zone": False}
4604
4605
4606class TimeAdd(Func, TimeUnit):
4607    arg_types = {"this": True, "expression": True, "unit": False}
4608
4609
4610class TimeSub(Func, TimeUnit):
4611    arg_types = {"this": True, "expression": True, "unit": False}
4612
4613
4614class TimeDiff(Func, TimeUnit):
4615    arg_types = {"this": True, "expression": True, "unit": False}
4616
4617
4618class TimeTrunc(Func, TimeUnit):
4619    arg_types = {"this": True, "unit": True, "zone": False}
4620
4621
4622class DateFromParts(Func):
4623    _sql_names = ["DATEFROMPARTS"]
4624    arg_types = {"year": True, "month": True, "day": True}
4625
4626
4627class DateStrToDate(Func):
4628    pass
4629
4630
4631class DateToDateStr(Func):
4632    pass
4633
4634
4635class DateToDi(Func):
4636    pass
4637
4638
4639# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4640class Date(Func):
4641    arg_types = {"this": False, "zone": False, "expressions": False}
4642    is_var_len_args = True
4643
4644
4645class Day(Func):
4646    pass
4647
4648
4649class Decode(Func):
4650    arg_types = {"this": True, "charset": True, "replace": False}
4651
4652
4653class DiToDate(Func):
4654    pass
4655
4656
4657class Encode(Func):
4658    arg_types = {"this": True, "charset": True}
4659
4660
4661class Exp(Func):
4662    pass
4663
4664
4665# https://docs.snowflake.com/en/sql-reference/functions/flatten
4666class Explode(Func):
4667    arg_types = {"this": True, "expressions": False}
4668    is_var_len_args = True
4669
4670
4671class ExplodeOuter(Explode):
4672    pass
4673
4674
4675class Posexplode(Explode):
4676    pass
4677
4678
4679class PosexplodeOuter(Posexplode):
4680    pass
4681
4682
4683class Floor(Func):
4684    arg_types = {"this": True, "decimals": False}
4685
4686
4687class FromBase64(Func):
4688    pass
4689
4690
4691class ToBase64(Func):
4692    pass
4693
4694
4695class Greatest(Func):
4696    arg_types = {"this": True, "expressions": False}
4697    is_var_len_args = True
4698
4699
4700class GroupConcat(AggFunc):
4701    arg_types = {"this": True, "separator": False}
4702
4703
4704class Hex(Func):
4705    pass
4706
4707
4708class Xor(Connector, Func):
4709    arg_types = {"this": False, "expression": False, "expressions": False}
4710
4711
4712class If(Func):
4713    arg_types = {"this": True, "true": True, "false": False}
4714
4715
4716class Nullif(Func):
4717    arg_types = {"this": True, "expression": True}
4718
4719
4720class Initcap(Func):
4721    arg_types = {"this": True, "expression": False}
4722
4723
4724class IsNan(Func):
4725    _sql_names = ["IS_NAN", "ISNAN"]
4726
4727
4728class IsInf(Func):
4729    _sql_names = ["IS_INF", "ISINF"]
4730
4731
4732class FormatJson(Expression):
4733    pass
4734
4735
4736class JSONKeyValue(Expression):
4737    arg_types = {"this": True, "expression": True}
4738
4739
4740class JSONObject(Func):
4741    arg_types = {
4742        "expressions": False,
4743        "null_handling": False,
4744        "unique_keys": False,
4745        "return_type": False,
4746        "encoding": False,
4747    }
4748
4749
4750# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4751class JSONArray(Func):
4752    arg_types = {
4753        "expressions": True,
4754        "null_handling": False,
4755        "return_type": False,
4756        "strict": False,
4757    }
4758
4759
4760# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4761class JSONArrayAgg(Func):
4762    arg_types = {
4763        "this": True,
4764        "order": False,
4765        "null_handling": False,
4766        "return_type": False,
4767        "strict": False,
4768    }
4769
4770
4771# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4772# Note: parsing of JSON column definitions is currently incomplete.
4773class JSONColumnDef(Expression):
4774    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4775
4776
4777class JSONSchema(Expression):
4778    arg_types = {"expressions": True}
4779
4780
4781# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4782class JSONTable(Func):
4783    arg_types = {
4784        "this": True,
4785        "schema": True,
4786        "path": False,
4787        "error_handling": False,
4788        "empty_handling": False,
4789    }
4790
4791
4792class OpenJSONColumnDef(Expression):
4793    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4794
4795
4796class OpenJSON(Func):
4797    arg_types = {"this": True, "path": False, "expressions": False}
4798
4799
4800class JSONBContains(Binary):
4801    _sql_names = ["JSONB_CONTAINS"]
4802
4803
4804class JSONExtract(Binary, Func):
4805    _sql_names = ["JSON_EXTRACT"]
4806
4807
4808class JSONExtractScalar(JSONExtract):
4809    _sql_names = ["JSON_EXTRACT_SCALAR"]
4810
4811
4812class JSONBExtract(JSONExtract):
4813    _sql_names = ["JSONB_EXTRACT"]
4814
4815
4816class JSONBExtractScalar(JSONExtract):
4817    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4818
4819
4820class JSONFormat(Func):
4821    arg_types = {"this": False, "options": False}
4822    _sql_names = ["JSON_FORMAT"]
4823
4824
4825# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4826class JSONArrayContains(Binary, Predicate, Func):
4827    _sql_names = ["JSON_ARRAY_CONTAINS"]
4828
4829
4830class ParseJSON(Func):
4831    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4832    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4833    arg_types = {"this": True, "expressions": False}
4834    is_var_len_args = True
4835
4836
4837class Least(Func):
4838    arg_types = {"this": True, "expressions": False}
4839    is_var_len_args = True
4840
4841
4842class Left(Func):
4843    arg_types = {"this": True, "expression": True}
4844
4845
4846class Right(Func):
4847    arg_types = {"this": True, "expression": True}
4848
4849
4850class Length(Func):
4851    _sql_names = ["LENGTH", "LEN"]
4852
4853
4854class Levenshtein(Func):
4855    arg_types = {
4856        "this": True,
4857        "expression": False,
4858        "ins_cost": False,
4859        "del_cost": False,
4860        "sub_cost": False,
4861    }
4862
4863
4864class Ln(Func):
4865    pass
4866
4867
4868class Log(Func):
4869    arg_types = {"this": True, "expression": False}
4870
4871
4872class Log2(Func):
4873    pass
4874
4875
4876class Log10(Func):
4877    pass
4878
4879
4880class LogicalOr(AggFunc):
4881    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4882
4883
4884class LogicalAnd(AggFunc):
4885    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4886
4887
4888class Lower(Func):
4889    _sql_names = ["LOWER", "LCASE"]
4890
4891
4892class Map(Func):
4893    arg_types = {"keys": False, "values": False}
4894
4895    @property
4896    def keys(self) -> t.List[Expression]:
4897        keys = self.args.get("keys")
4898        return keys.expressions if keys else []
4899
4900    @property
4901    def values(self) -> t.List[Expression]:
4902        values = self.args.get("values")
4903        return values.expressions if values else []
4904
4905
4906class MapFromEntries(Func):
4907    pass
4908
4909
4910class StarMap(Func):
4911    pass
4912
4913
4914class VarMap(Func):
4915    arg_types = {"keys": True, "values": True}
4916    is_var_len_args = True
4917
4918    @property
4919    def keys(self) -> t.List[Expression]:
4920        return self.args["keys"].expressions
4921
4922    @property
4923    def values(self) -> t.List[Expression]:
4924        return self.args["values"].expressions
4925
4926
4927# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4928class MatchAgainst(Func):
4929    arg_types = {"this": True, "expressions": True, "modifier": False}
4930
4931
4932class Max(AggFunc):
4933    arg_types = {"this": True, "expressions": False}
4934    is_var_len_args = True
4935
4936
4937class MD5(Func):
4938    _sql_names = ["MD5"]
4939
4940
4941# Represents the variant of the MD5 function that returns a binary value
4942class MD5Digest(Func):
4943    _sql_names = ["MD5_DIGEST"]
4944
4945
4946class Min(AggFunc):
4947    arg_types = {"this": True, "expressions": False}
4948    is_var_len_args = True
4949
4950
4951class Month(Func):
4952    pass
4953
4954
4955class Nvl2(Func):
4956    arg_types = {"this": True, "true": True, "false": False}
4957
4958
4959# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4960class Predict(Func):
4961    arg_types = {"this": True, "expression": True, "params_struct": False}
4962
4963
4964class Pow(Binary, Func):
4965    _sql_names = ["POWER", "POW"]
4966
4967
4968class PercentileCont(AggFunc):
4969    arg_types = {"this": True, "expression": False}
4970
4971
4972class PercentileDisc(AggFunc):
4973    arg_types = {"this": True, "expression": False}
4974
4975
4976class Quantile(AggFunc):
4977    arg_types = {"this": True, "quantile": True}
4978
4979
4980class ApproxQuantile(Quantile):
4981    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4982
4983
4984class RangeN(Func):
4985    arg_types = {"this": True, "expressions": True, "each": False}
4986
4987
4988class ReadCSV(Func):
4989    _sql_names = ["READ_CSV"]
4990    is_var_len_args = True
4991    arg_types = {"this": True, "expressions": False}
4992
4993
4994class Reduce(Func):
4995    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4996
4997
4998class RegexpExtract(Func):
4999    arg_types = {
5000        "this": True,
5001        "expression": True,
5002        "position": False,
5003        "occurrence": False,
5004        "parameters": False,
5005        "group": False,
5006    }
5007
5008
5009class RegexpReplace(Func):
5010    arg_types = {
5011        "this": True,
5012        "expression": True,
5013        "replacement": True,
5014        "position": False,
5015        "occurrence": False,
5016        "parameters": False,
5017        "modifiers": False,
5018    }
5019
5020
5021class RegexpLike(Binary, Func):
5022    arg_types = {"this": True, "expression": True, "flag": False}
5023
5024
5025class RegexpILike(Binary, Func):
5026    arg_types = {"this": True, "expression": True, "flag": False}
5027
5028
5029# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5030# limit is the number of times a pattern is applied
5031class RegexpSplit(Func):
5032    arg_types = {"this": True, "expression": True, "limit": False}
5033
5034
5035class Repeat(Func):
5036    arg_types = {"this": True, "times": True}
5037
5038
5039class Round(Func):
5040    arg_types = {"this": True, "decimals": False}
5041
5042
5043class RowNumber(Func):
5044    arg_types: t.Dict[str, t.Any] = {}
5045
5046
5047class SafeDivide(Func):
5048    arg_types = {"this": True, "expression": True}
5049
5050
5051class SHA(Func):
5052    _sql_names = ["SHA", "SHA1"]
5053
5054
5055class SHA2(Func):
5056    _sql_names = ["SHA2"]
5057    arg_types = {"this": True, "length": False}
5058
5059
5060class SortArray(Func):
5061    arg_types = {"this": True, "asc": False}
5062
5063
5064class Split(Func):
5065    arg_types = {"this": True, "expression": True, "limit": False}
5066
5067
5068# Start may be omitted in the case of postgres
5069# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5070class Substring(Func):
5071    arg_types = {"this": True, "start": False, "length": False}
5072
5073
5074class StandardHash(Func):
5075    arg_types = {"this": True, "expression": False}
5076
5077
5078class StartsWith(Func):
5079    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5080    arg_types = {"this": True, "expression": True}
5081
5082
5083class StrPosition(Func):
5084    arg_types = {
5085        "this": True,
5086        "substr": True,
5087        "position": False,
5088        "instance": False,
5089    }
5090
5091
5092class StrToDate(Func):
5093    arg_types = {"this": True, "format": True}
5094
5095
5096class StrToTime(Func):
5097    arg_types = {"this": True, "format": True, "zone": False}
5098
5099
5100# Spark allows unix_timestamp()
5101# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5102class StrToUnix(Func):
5103    arg_types = {"this": False, "format": False}
5104
5105
5106# https://prestodb.io/docs/current/functions/string.html
5107# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5108class StrToMap(Func):
5109    arg_types = {
5110        "this": True,
5111        "pair_delim": False,
5112        "key_value_delim": False,
5113        "duplicate_resolution_callback": False,
5114    }
5115
5116
5117class NumberToStr(Func):
5118    arg_types = {"this": True, "format": True, "culture": False}
5119
5120
5121class FromBase(Func):
5122    arg_types = {"this": True, "expression": True}
5123
5124
5125class Struct(Func):
5126    arg_types = {"expressions": False}
5127    is_var_len_args = True
5128
5129
5130class StructExtract(Func):
5131    arg_types = {"this": True, "expression": True}
5132
5133
5134# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5135# https://docs.snowflake.com/en/sql-reference/functions/insert
5136class Stuff(Func):
5137    _sql_names = ["STUFF", "INSERT"]
5138    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5139
5140
5141class Sum(AggFunc):
5142    pass
5143
5144
5145class Sqrt(Func):
5146    pass
5147
5148
5149class Stddev(AggFunc):
5150    pass
5151
5152
5153class StddevPop(AggFunc):
5154    pass
5155
5156
5157class StddevSamp(AggFunc):
5158    pass
5159
5160
5161class TimeToStr(Func):
5162    arg_types = {"this": True, "format": True, "culture": False}
5163
5164
5165class TimeToTimeStr(Func):
5166    pass
5167
5168
5169class TimeToUnix(Func):
5170    pass
5171
5172
5173class TimeStrToDate(Func):
5174    pass
5175
5176
5177class TimeStrToTime(Func):
5178    pass
5179
5180
5181class TimeStrToUnix(Func):
5182    pass
5183
5184
5185class Trim(Func):
5186    arg_types = {
5187        "this": True,
5188        "expression": False,
5189        "position": False,
5190        "collation": False,
5191    }
5192
5193
5194class TsOrDsAdd(Func, TimeUnit):
5195    # return_type is used to correctly cast the arguments of this expression when transpiling it
5196    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5197
5198    @property
5199    def return_type(self) -> DataType:
5200        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5201
5202
5203class TsOrDsDiff(Func, TimeUnit):
5204    arg_types = {"this": True, "expression": True, "unit": False}
5205
5206
5207class TsOrDsToDateStr(Func):
5208    pass
5209
5210
5211class TsOrDsToDate(Func):
5212    arg_types = {"this": True, "format": False}
5213
5214
5215class TsOrDiToDi(Func):
5216    pass
5217
5218
5219class Unhex(Func):
5220    pass
5221
5222
5223class UnixToStr(Func):
5224    arg_types = {"this": True, "format": False}
5225
5226
5227# https://prestodb.io/docs/current/functions/datetime.html
5228# presto has weird zone/hours/minutes
5229class UnixToTime(Func):
5230    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5231
5232    SECONDS = Literal.string("seconds")
5233    MILLIS = Literal.string("millis")
5234    MICROS = Literal.string("micros")
5235    NANOS = Literal.string("nanos")
5236
5237
5238class UnixToTimeStr(Func):
5239    pass
5240
5241
5242class Upper(Func):
5243    _sql_names = ["UPPER", "UCASE"]
5244
5245
5246class Variance(AggFunc):
5247    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5248
5249
5250class VariancePop(AggFunc):
5251    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5252
5253
5254class Week(Func):
5255    arg_types = {"this": True, "mode": False}
5256
5257
5258class XMLTable(Func):
5259    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5260
5261
5262class Year(Func):
5263    pass
5264
5265
5266class Use(Expression):
5267    arg_types = {"this": True, "kind": False}
5268
5269
5270class Merge(Expression):
5271    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5272
5273
5274class When(Func):
5275    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5276
5277
5278# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5279# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5280class NextValueFor(Func):
5281    arg_types = {"this": True, "order": False}
5282
5283
5284def _norm_arg(arg):
5285    return arg.lower() if type(arg) is str else arg
5286
5287
5288ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5289
5290
5291# Helpers
5292@t.overload
5293def maybe_parse(
5294    sql_or_expression: ExpOrStr,
5295    *,
5296    into: t.Type[E],
5297    dialect: DialectType = None,
5298    prefix: t.Optional[str] = None,
5299    copy: bool = False,
5300    **opts,
5301) -> E:
5302    ...
5303
5304
5305@t.overload
5306def maybe_parse(
5307    sql_or_expression: str | E,
5308    *,
5309    into: t.Optional[IntoType] = None,
5310    dialect: DialectType = None,
5311    prefix: t.Optional[str] = None,
5312    copy: bool = False,
5313    **opts,
5314) -> E:
5315    ...
5316
5317
5318def maybe_parse(
5319    sql_or_expression: ExpOrStr,
5320    *,
5321    into: t.Optional[IntoType] = None,
5322    dialect: DialectType = None,
5323    prefix: t.Optional[str] = None,
5324    copy: bool = False,
5325    **opts,
5326) -> Expression:
5327    """Gracefully handle a possible string or expression.
5328
5329    Example:
5330        >>> maybe_parse("1")
5331        (LITERAL this: 1, is_string: False)
5332        >>> maybe_parse(to_identifier("x"))
5333        (IDENTIFIER this: x, quoted: False)
5334
5335    Args:
5336        sql_or_expression: the SQL code string or an expression
5337        into: the SQLGlot Expression to parse into
5338        dialect: the dialect used to parse the input expressions (in the case that an
5339            input expression is a SQL string).
5340        prefix: a string to prefix the sql with before it gets parsed
5341            (automatically includes a space)
5342        copy: whether or not to copy the expression.
5343        **opts: other options to use to parse the input expressions (again, in the case
5344            that an input expression is a SQL string).
5345
5346    Returns:
5347        Expression: the parsed or given expression.
5348    """
5349    if isinstance(sql_or_expression, Expression):
5350        if copy:
5351            return sql_or_expression.copy()
5352        return sql_or_expression
5353
5354    if sql_or_expression is None:
5355        raise ParseError(f"SQL cannot be None")
5356
5357    import sqlglot
5358
5359    sql = str(sql_or_expression)
5360    if prefix:
5361        sql = f"{prefix} {sql}"
5362
5363    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5364
5365
5366@t.overload
5367def maybe_copy(instance: None, copy: bool = True) -> None:
5368    ...
5369
5370
5371@t.overload
5372def maybe_copy(instance: E, copy: bool = True) -> E:
5373    ...
5374
5375
5376def maybe_copy(instance, copy=True):
5377    return instance.copy() if copy and instance else instance
5378
5379
5380def _is_wrong_expression(expression, into):
5381    return isinstance(expression, Expression) and not isinstance(expression, into)
5382
5383
5384def _apply_builder(
5385    expression,
5386    instance,
5387    arg,
5388    copy=True,
5389    prefix=None,
5390    into=None,
5391    dialect=None,
5392    into_arg="this",
5393    **opts,
5394):
5395    if _is_wrong_expression(expression, into):
5396        expression = into(**{into_arg: expression})
5397    instance = maybe_copy(instance, copy)
5398    expression = maybe_parse(
5399        sql_or_expression=expression,
5400        prefix=prefix,
5401        into=into,
5402        dialect=dialect,
5403        **opts,
5404    )
5405    instance.set(arg, expression)
5406    return instance
5407
5408
5409def _apply_child_list_builder(
5410    *expressions,
5411    instance,
5412    arg,
5413    append=True,
5414    copy=True,
5415    prefix=None,
5416    into=None,
5417    dialect=None,
5418    properties=None,
5419    **opts,
5420):
5421    instance = maybe_copy(instance, copy)
5422    parsed = []
5423    for expression in expressions:
5424        if expression is not None:
5425            if _is_wrong_expression(expression, into):
5426                expression = into(expressions=[expression])
5427
5428            expression = maybe_parse(
5429                expression,
5430                into=into,
5431                dialect=dialect,
5432                prefix=prefix,
5433                **opts,
5434            )
5435            parsed.extend(expression.expressions)
5436
5437    existing = instance.args.get(arg)
5438    if append and existing:
5439        parsed = existing.expressions + parsed
5440
5441    child = into(expressions=parsed)
5442    for k, v in (properties or {}).items():
5443        child.set(k, v)
5444    instance.set(arg, child)
5445
5446    return instance
5447
5448
5449def _apply_list_builder(
5450    *expressions,
5451    instance,
5452    arg,
5453    append=True,
5454    copy=True,
5455    prefix=None,
5456    into=None,
5457    dialect=None,
5458    **opts,
5459):
5460    inst = maybe_copy(instance, copy)
5461
5462    expressions = [
5463        maybe_parse(
5464            sql_or_expression=expression,
5465            into=into,
5466            prefix=prefix,
5467            dialect=dialect,
5468            **opts,
5469        )
5470        for expression in expressions
5471        if expression is not None
5472    ]
5473
5474    existing_expressions = inst.args.get(arg)
5475    if append and existing_expressions:
5476        expressions = existing_expressions + expressions
5477
5478    inst.set(arg, expressions)
5479    return inst
5480
5481
5482def _apply_conjunction_builder(
5483    *expressions,
5484    instance,
5485    arg,
5486    into=None,
5487    append=True,
5488    copy=True,
5489    dialect=None,
5490    **opts,
5491):
5492    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5493    if not expressions:
5494        return instance
5495
5496    inst = maybe_copy(instance, copy)
5497
5498    existing = inst.args.get(arg)
5499    if append and existing is not None:
5500        expressions = [existing.this if into else existing] + list(expressions)
5501
5502    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5503
5504    inst.set(arg, into(this=node) if into else node)
5505    return inst
5506
5507
5508def _apply_cte_builder(
5509    instance: E,
5510    alias: ExpOrStr,
5511    as_: ExpOrStr,
5512    recursive: t.Optional[bool] = None,
5513    append: bool = True,
5514    dialect: DialectType = None,
5515    copy: bool = True,
5516    **opts,
5517) -> E:
5518    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5519    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5520    cte = CTE(this=as_expression, alias=alias_expression)
5521    return _apply_child_list_builder(
5522        cte,
5523        instance=instance,
5524        arg="with",
5525        append=append,
5526        copy=copy,
5527        into=With,
5528        properties={"recursive": recursive or False},
5529    )
5530
5531
5532def _combine(
5533    expressions: t.Sequence[t.Optional[ExpOrStr]],
5534    operator: t.Type[Connector],
5535    dialect: DialectType = None,
5536    copy: bool = True,
5537    **opts,
5538) -> Expression:
5539    conditions = [
5540        condition(expression, dialect=dialect, copy=copy, **opts)
5541        for expression in expressions
5542        if expression is not None
5543    ]
5544
5545    this, *rest = conditions
5546    if rest:
5547        this = _wrap(this, Connector)
5548    for expression in rest:
5549        this = operator(this=this, expression=_wrap(expression, Connector))
5550
5551    return this
5552
5553
5554def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5555    return Paren(this=expression) if isinstance(expression, kind) else expression
5556
5557
5558def union(
5559    left: ExpOrStr,
5560    right: ExpOrStr,
5561    distinct: bool = True,
5562    dialect: DialectType = None,
5563    copy: bool = True,
5564    **opts,
5565) -> Union:
5566    """
5567    Initializes a syntax tree from one UNION expression.
5568
5569    Example:
5570        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5571        'SELECT * FROM foo UNION SELECT * FROM bla'
5572
5573    Args:
5574        left: the SQL code string corresponding to the left-hand side.
5575            If an `Expression` instance is passed, it will be used as-is.
5576        right: the SQL code string corresponding to the right-hand side.
5577            If an `Expression` instance is passed, it will be used as-is.
5578        distinct: set the DISTINCT flag if and only if this is true.
5579        dialect: the dialect used to parse the input expression.
5580        copy: whether or not to copy the expression.
5581        opts: other options to use to parse the input expressions.
5582
5583    Returns:
5584        The new Union instance.
5585    """
5586    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5587    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5588
5589    return Union(this=left, expression=right, distinct=distinct)
5590
5591
5592def intersect(
5593    left: ExpOrStr,
5594    right: ExpOrStr,
5595    distinct: bool = True,
5596    dialect: DialectType = None,
5597    copy: bool = True,
5598    **opts,
5599) -> Intersect:
5600    """
5601    Initializes a syntax tree from one INTERSECT expression.
5602
5603    Example:
5604        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5605        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5606
5607    Args:
5608        left: the SQL code string corresponding to the left-hand side.
5609            If an `Expression` instance is passed, it will be used as-is.
5610        right: the SQL code string corresponding to the right-hand side.
5611            If an `Expression` instance is passed, it will be used as-is.
5612        distinct: set the DISTINCT flag if and only if this is true.
5613        dialect: the dialect used to parse the input expression.
5614        copy: whether or not to copy the expression.
5615        opts: other options to use to parse the input expressions.
5616
5617    Returns:
5618        The new Intersect instance.
5619    """
5620    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5621    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5622
5623    return Intersect(this=left, expression=right, distinct=distinct)
5624
5625
5626def except_(
5627    left: ExpOrStr,
5628    right: ExpOrStr,
5629    distinct: bool = True,
5630    dialect: DialectType = None,
5631    copy: bool = True,
5632    **opts,
5633) -> Except:
5634    """
5635    Initializes a syntax tree from one EXCEPT expression.
5636
5637    Example:
5638        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5639        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5640
5641    Args:
5642        left: the SQL code string corresponding to the left-hand side.
5643            If an `Expression` instance is passed, it will be used as-is.
5644        right: the SQL code string corresponding to the right-hand side.
5645            If an `Expression` instance is passed, it will be used as-is.
5646        distinct: set the DISTINCT flag if and only if this is true.
5647        dialect: the dialect used to parse the input expression.
5648        copy: whether or not to copy the expression.
5649        opts: other options to use to parse the input expressions.
5650
5651    Returns:
5652        The new Except instance.
5653    """
5654    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5655    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5656
5657    return Except(this=left, expression=right, distinct=distinct)
5658
5659
5660def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5661    """
5662    Initializes a syntax tree from one or multiple SELECT expressions.
5663
5664    Example:
5665        >>> select("col1", "col2").from_("tbl").sql()
5666        'SELECT col1, col2 FROM tbl'
5667
5668    Args:
5669        *expressions: the SQL code string to parse as the expressions of a
5670            SELECT statement. If an Expression instance is passed, this is used as-is.
5671        dialect: the dialect used to parse the input expressions (in the case that an
5672            input expression is a SQL string).
5673        **opts: other options to use to parse the input expressions (again, in the case
5674            that an input expression is a SQL string).
5675
5676    Returns:
5677        Select: the syntax tree for the SELECT statement.
5678    """
5679    return Select().select(*expressions, dialect=dialect, **opts)
5680
5681
5682def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5683    """
5684    Initializes a syntax tree from a FROM expression.
5685
5686    Example:
5687        >>> from_("tbl").select("col1", "col2").sql()
5688        'SELECT col1, col2 FROM tbl'
5689
5690    Args:
5691        *expression: the SQL code string to parse as the FROM expressions of a
5692            SELECT statement. If an Expression instance is passed, this is used as-is.
5693        dialect: the dialect used to parse the input expression (in the case that the
5694            input expression is a SQL string).
5695        **opts: other options to use to parse the input expressions (again, in the case
5696            that the input expression is a SQL string).
5697
5698    Returns:
5699        Select: the syntax tree for the SELECT statement.
5700    """
5701    return Select().from_(expression, dialect=dialect, **opts)
5702
5703
5704def update(
5705    table: str | Table,
5706    properties: dict,
5707    where: t.Optional[ExpOrStr] = None,
5708    from_: t.Optional[ExpOrStr] = None,
5709    dialect: DialectType = None,
5710    **opts,
5711) -> Update:
5712    """
5713    Creates an update statement.
5714
5715    Example:
5716        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5717        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5718
5719    Args:
5720        *properties: dictionary of properties to set which are
5721            auto converted to sql objects eg None -> NULL
5722        where: sql conditional parsed into a WHERE statement
5723        from_: sql statement parsed into a FROM statement
5724        dialect: the dialect used to parse the input expressions.
5725        **opts: other options to use to parse the input expressions.
5726
5727    Returns:
5728        Update: the syntax tree for the UPDATE statement.
5729    """
5730    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5731    update_expr.set(
5732        "expressions",
5733        [
5734            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5735            for k, v in properties.items()
5736        ],
5737    )
5738    if from_:
5739        update_expr.set(
5740            "from",
5741            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5742        )
5743    if isinstance(where, Condition):
5744        where = Where(this=where)
5745    if where:
5746        update_expr.set(
5747            "where",
5748            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5749        )
5750    return update_expr
5751
5752
5753def delete(
5754    table: ExpOrStr,
5755    where: t.Optional[ExpOrStr] = None,
5756    returning: t.Optional[ExpOrStr] = None,
5757    dialect: DialectType = None,
5758    **opts,
5759) -> Delete:
5760    """
5761    Builds a delete statement.
5762
5763    Example:
5764        >>> delete("my_table", where="id > 1").sql()
5765        'DELETE FROM my_table WHERE id > 1'
5766
5767    Args:
5768        where: sql conditional parsed into a WHERE statement
5769        returning: sql conditional parsed into a RETURNING statement
5770        dialect: the dialect used to parse the input expressions.
5771        **opts: other options to use to parse the input expressions.
5772
5773    Returns:
5774        Delete: the syntax tree for the DELETE statement.
5775    """
5776    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5777    if where:
5778        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5779    if returning:
5780        delete_expr = t.cast(
5781            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5782        )
5783    return delete_expr
5784
5785
5786def insert(
5787    expression: ExpOrStr,
5788    into: ExpOrStr,
5789    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5790    overwrite: t.Optional[bool] = None,
5791    returning: t.Optional[ExpOrStr] = None,
5792    dialect: DialectType = None,
5793    copy: bool = True,
5794    **opts,
5795) -> Insert:
5796    """
5797    Builds an INSERT statement.
5798
5799    Example:
5800        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5801        'INSERT INTO tbl VALUES (1, 2, 3)'
5802
5803    Args:
5804        expression: the sql string or expression of the INSERT statement
5805        into: the tbl to insert data to.
5806        columns: optionally the table's column names.
5807        overwrite: whether to INSERT OVERWRITE or not.
5808        returning: sql conditional parsed into a RETURNING statement
5809        dialect: the dialect used to parse the input expressions.
5810        copy: whether or not to copy the expression.
5811        **opts: other options to use to parse the input expressions.
5812
5813    Returns:
5814        Insert: the syntax tree for the INSERT statement.
5815    """
5816    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5817    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5818
5819    if columns:
5820        this = _apply_list_builder(
5821            *columns,
5822            instance=Schema(this=this),
5823            arg="expressions",
5824            into=Identifier,
5825            copy=False,
5826            dialect=dialect,
5827            **opts,
5828        )
5829
5830    insert = Insert(this=this, expression=expr, overwrite=overwrite)
5831
5832    if returning:
5833        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
5834
5835    return insert
5836
5837
5838def condition(
5839    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5840) -> Condition:
5841    """
5842    Initialize a logical condition expression.
5843
5844    Example:
5845        >>> condition("x=1").sql()
5846        'x = 1'
5847
5848        This is helpful for composing larger logical syntax trees:
5849        >>> where = condition("x=1")
5850        >>> where = where.and_("y=1")
5851        >>> Select().from_("tbl").select("*").where(where).sql()
5852        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5853
5854    Args:
5855        *expression: the SQL code string to parse.
5856            If an Expression instance is passed, this is used as-is.
5857        dialect: the dialect used to parse the input expression (in the case that the
5858            input expression is a SQL string).
5859        copy: Whether or not to copy `expression` (only applies to expressions).
5860        **opts: other options to use to parse the input expressions (again, in the case
5861            that the input expression is a SQL string).
5862
5863    Returns:
5864        The new Condition instance
5865    """
5866    return maybe_parse(
5867        expression,
5868        into=Condition,
5869        dialect=dialect,
5870        copy=copy,
5871        **opts,
5872    )
5873
5874
5875def and_(
5876    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5877) -> Condition:
5878    """
5879    Combine multiple conditions with an AND logical operator.
5880
5881    Example:
5882        >>> and_("x=1", and_("y=1", "z=1")).sql()
5883        'x = 1 AND (y = 1 AND z = 1)'
5884
5885    Args:
5886        *expressions: the SQL code strings to parse.
5887            If an Expression instance is passed, this is used as-is.
5888        dialect: the dialect used to parse the input expression.
5889        copy: whether or not to copy `expressions` (only applies to Expressions).
5890        **opts: other options to use to parse the input expressions.
5891
5892    Returns:
5893        And: the new condition
5894    """
5895    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5896
5897
5898def or_(
5899    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5900) -> Condition:
5901    """
5902    Combine multiple conditions with an OR logical operator.
5903
5904    Example:
5905        >>> or_("x=1", or_("y=1", "z=1")).sql()
5906        'x = 1 OR (y = 1 OR z = 1)'
5907
5908    Args:
5909        *expressions: the SQL code strings to parse.
5910            If an Expression instance is passed, this is used as-is.
5911        dialect: the dialect used to parse the input expression.
5912        copy: whether or not to copy `expressions` (only applies to Expressions).
5913        **opts: other options to use to parse the input expressions.
5914
5915    Returns:
5916        Or: the new condition
5917    """
5918    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5919
5920
5921def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5922    """
5923    Wrap a condition with a NOT operator.
5924
5925    Example:
5926        >>> not_("this_suit='black'").sql()
5927        "NOT this_suit = 'black'"
5928
5929    Args:
5930        expression: the SQL code string to parse.
5931            If an Expression instance is passed, this is used as-is.
5932        dialect: the dialect used to parse the input expression.
5933        copy: whether to copy the expression or not.
5934        **opts: other options to use to parse the input expressions.
5935
5936    Returns:
5937        The new condition.
5938    """
5939    this = condition(
5940        expression,
5941        dialect=dialect,
5942        copy=copy,
5943        **opts,
5944    )
5945    return Not(this=_wrap(this, Connector))
5946
5947
5948def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5949    """
5950    Wrap an expression in parentheses.
5951
5952    Example:
5953        >>> paren("5 + 3").sql()
5954        '(5 + 3)'
5955
5956    Args:
5957        expression: the SQL code string to parse.
5958            If an Expression instance is passed, this is used as-is.
5959        copy: whether to copy the expression or not.
5960
5961    Returns:
5962        The wrapped expression.
5963    """
5964    return Paren(this=maybe_parse(expression, copy=copy))
5965
5966
5967SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5968
5969
5970@t.overload
5971def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5972    ...
5973
5974
5975@t.overload
5976def to_identifier(
5977    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5978) -> Identifier:
5979    ...
5980
5981
5982def to_identifier(name, quoted=None, copy=True):
5983    """Builds an identifier.
5984
5985    Args:
5986        name: The name to turn into an identifier.
5987        quoted: Whether or not force quote the identifier.
5988        copy: Whether or not to copy name if it's an Identifier.
5989
5990    Returns:
5991        The identifier ast node.
5992    """
5993
5994    if name is None:
5995        return None
5996
5997    if isinstance(name, Identifier):
5998        identifier = maybe_copy(name, copy)
5999    elif isinstance(name, str):
6000        identifier = Identifier(
6001            this=name,
6002            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6003        )
6004    else:
6005        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6006    return identifier
6007
6008
6009def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6010    """
6011    Parses a given string into an identifier.
6012
6013    Args:
6014        name: The name to parse into an identifier.
6015        dialect: The dialect to parse against.
6016
6017    Returns:
6018        The identifier ast node.
6019    """
6020    try:
6021        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6022    except ParseError:
6023        expression = to_identifier(name)
6024
6025    return expression
6026
6027
6028INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6029
6030
6031def to_interval(interval: str | Literal) -> Interval:
6032    """Builds an interval expression from a string like '1 day' or '5 months'."""
6033    if isinstance(interval, Literal):
6034        if not interval.is_string:
6035            raise ValueError("Invalid interval string.")
6036
6037        interval = interval.this
6038
6039    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6040
6041    if not interval_parts:
6042        raise ValueError("Invalid interval string.")
6043
6044    return Interval(
6045        this=Literal.string(interval_parts.group(1)),
6046        unit=Var(this=interval_parts.group(2)),
6047    )
6048
6049
6050@t.overload
6051def to_table(sql_path: str | Table, **kwargs) -> Table:
6052    ...
6053
6054
6055@t.overload
6056def to_table(sql_path: None, **kwargs) -> None:
6057    ...
6058
6059
6060def to_table(
6061    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6062) -> t.Optional[Table]:
6063    """
6064    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6065    If a table is passed in then that table is returned.
6066
6067    Args:
6068        sql_path: a `[catalog].[schema].[table]` string.
6069        dialect: the source dialect according to which the table name will be parsed.
6070        copy: Whether or not to copy a table if it is passed in.
6071        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6072
6073    Returns:
6074        A table expression.
6075    """
6076    if sql_path is None or isinstance(sql_path, Table):
6077        return maybe_copy(sql_path, copy=copy)
6078    if not isinstance(sql_path, str):
6079        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6080
6081    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6082    if table:
6083        for k, v in kwargs.items():
6084            table.set(k, v)
6085
6086    return table
6087
6088
6089def to_column(sql_path: str | Column, **kwargs) -> Column:
6090    """
6091    Create a column from a `[table].[column]` sql path. Schema is optional.
6092
6093    If a column is passed in then that column is returned.
6094
6095    Args:
6096        sql_path: `[table].[column]` string
6097    Returns:
6098        Table: A column expression
6099    """
6100    if sql_path is None or isinstance(sql_path, Column):
6101        return sql_path
6102    if not isinstance(sql_path, str):
6103        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6104    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6105
6106
6107def alias_(
6108    expression: ExpOrStr,
6109    alias: str | Identifier,
6110    table: bool | t.Sequence[str | Identifier] = False,
6111    quoted: t.Optional[bool] = None,
6112    dialect: DialectType = None,
6113    copy: bool = True,
6114    **opts,
6115):
6116    """Create an Alias expression.
6117
6118    Example:
6119        >>> alias_('foo', 'bar').sql()
6120        'foo AS bar'
6121
6122        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6123        '(SELECT 1, 2) AS bar(a, b)'
6124
6125    Args:
6126        expression: the SQL code strings to parse.
6127            If an Expression instance is passed, this is used as-is.
6128        alias: the alias name to use. If the name has
6129            special characters it is quoted.
6130        table: Whether or not to create a table alias, can also be a list of columns.
6131        quoted: whether or not to quote the alias
6132        dialect: the dialect used to parse the input expression.
6133        copy: Whether or not to copy the expression.
6134        **opts: other options to use to parse the input expressions.
6135
6136    Returns:
6137        Alias: the aliased expression
6138    """
6139    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6140    alias = to_identifier(alias, quoted=quoted)
6141
6142    if table:
6143        table_alias = TableAlias(this=alias)
6144        exp.set("alias", table_alias)
6145
6146        if not isinstance(table, bool):
6147            for column in table:
6148                table_alias.append("columns", to_identifier(column, quoted=quoted))
6149
6150        return exp
6151
6152    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6153    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6154    # for the complete Window expression.
6155    #
6156    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6157
6158    if "alias" in exp.arg_types and not isinstance(exp, Window):
6159        exp.set("alias", alias)
6160        return exp
6161    return Alias(this=exp, alias=alias)
6162
6163
6164def subquery(
6165    expression: ExpOrStr,
6166    alias: t.Optional[Identifier | str] = None,
6167    dialect: DialectType = None,
6168    **opts,
6169) -> Select:
6170    """
6171    Build a subquery expression.
6172
6173    Example:
6174        >>> subquery('select x from tbl', 'bar').select('x').sql()
6175        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6176
6177    Args:
6178        expression: the SQL code strings to parse.
6179            If an Expression instance is passed, this is used as-is.
6180        alias: the alias name to use.
6181        dialect: the dialect used to parse the input expression.
6182        **opts: other options to use to parse the input expressions.
6183
6184    Returns:
6185        A new Select instance with the subquery expression included.
6186    """
6187
6188    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6189    return Select().from_(expression, dialect=dialect, **opts)
6190
6191
6192def column(
6193    col: str | Identifier,
6194    table: t.Optional[str | Identifier] = None,
6195    db: t.Optional[str | Identifier] = None,
6196    catalog: t.Optional[str | Identifier] = None,
6197    quoted: t.Optional[bool] = None,
6198) -> Column:
6199    """
6200    Build a Column.
6201
6202    Args:
6203        col: Column name.
6204        table: Table name.
6205        db: Database name.
6206        catalog: Catalog name.
6207        quoted: Whether to force quotes on the column's identifiers.
6208
6209    Returns:
6210        The new Column instance.
6211    """
6212    return Column(
6213        this=to_identifier(col, quoted=quoted),
6214        table=to_identifier(table, quoted=quoted),
6215        db=to_identifier(db, quoted=quoted),
6216        catalog=to_identifier(catalog, quoted=quoted),
6217    )
6218
6219
6220def cast(expression: ExpOrStr, to: DATA_TYPE, **opts) -> Cast:
6221    """Cast an expression to a data type.
6222
6223    Example:
6224        >>> cast('x + 1', 'int').sql()
6225        'CAST(x + 1 AS INT)'
6226
6227    Args:
6228        expression: The expression to cast.
6229        to: The datatype to cast to.
6230
6231    Returns:
6232        The new Cast instance.
6233    """
6234    expression = maybe_parse(expression, **opts)
6235    data_type = DataType.build(to, **opts)
6236    expression = Cast(this=expression, to=data_type)
6237    expression.type = data_type
6238    return expression
6239
6240
6241def table_(
6242    table: Identifier | str,
6243    db: t.Optional[Identifier | str] = None,
6244    catalog: t.Optional[Identifier | str] = None,
6245    quoted: t.Optional[bool] = None,
6246    alias: t.Optional[Identifier | str] = None,
6247) -> Table:
6248    """Build a Table.
6249
6250    Args:
6251        table: Table name.
6252        db: Database name.
6253        catalog: Catalog name.
6254        quote: Whether to force quotes on the table's identifiers.
6255        alias: Table's alias.
6256
6257    Returns:
6258        The new Table instance.
6259    """
6260    return Table(
6261        this=to_identifier(table, quoted=quoted) if table else None,
6262        db=to_identifier(db, quoted=quoted) if db else None,
6263        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6264        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6265    )
6266
6267
6268def values(
6269    values: t.Iterable[t.Tuple[t.Any, ...]],
6270    alias: t.Optional[str] = None,
6271    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6272) -> Values:
6273    """Build VALUES statement.
6274
6275    Example:
6276        >>> values([(1, '2')]).sql()
6277        "VALUES (1, '2')"
6278
6279    Args:
6280        values: values statements that will be converted to SQL
6281        alias: optional alias
6282        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6283         If either are provided then an alias is also required.
6284
6285    Returns:
6286        Values: the Values expression object
6287    """
6288    if columns and not alias:
6289        raise ValueError("Alias is required when providing columns")
6290
6291    return Values(
6292        expressions=[convert(tup) for tup in values],
6293        alias=(
6294            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6295            if columns
6296            else (TableAlias(this=to_identifier(alias)) if alias else None)
6297        ),
6298    )
6299
6300
6301def var(name: t.Optional[ExpOrStr]) -> Var:
6302    """Build a SQL variable.
6303
6304    Example:
6305        >>> repr(var('x'))
6306        '(VAR this: x)'
6307
6308        >>> repr(var(column('x', table='y')))
6309        '(VAR this: x)'
6310
6311    Args:
6312        name: The name of the var or an expression who's name will become the var.
6313
6314    Returns:
6315        The new variable node.
6316    """
6317    if not name:
6318        raise ValueError("Cannot convert empty name into var.")
6319
6320    if isinstance(name, Expression):
6321        name = name.name
6322    return Var(this=name)
6323
6324
6325def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6326    """Build ALTER TABLE... RENAME... expression
6327
6328    Args:
6329        old_name: The old name of the table
6330        new_name: The new name of the table
6331
6332    Returns:
6333        Alter table expression
6334    """
6335    old_table = to_table(old_name)
6336    new_table = to_table(new_name)
6337    return AlterTable(
6338        this=old_table,
6339        actions=[
6340            RenameTable(this=new_table),
6341        ],
6342    )
6343
6344
6345def convert(value: t.Any, copy: bool = False) -> Expression:
6346    """Convert a python value into an expression object.
6347
6348    Raises an error if a conversion is not possible.
6349
6350    Args:
6351        value: A python object.
6352        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6353
6354    Returns:
6355        Expression: the equivalent expression object.
6356    """
6357    if isinstance(value, Expression):
6358        return maybe_copy(value, copy)
6359    if isinstance(value, str):
6360        return Literal.string(value)
6361    if isinstance(value, bool):
6362        return Boolean(this=value)
6363    if value is None or (isinstance(value, float) and math.isnan(value)):
6364        return NULL
6365    if isinstance(value, numbers.Number):
6366        return Literal.number(value)
6367    if isinstance(value, datetime.datetime):
6368        datetime_literal = Literal.string(
6369            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6370        )
6371        return TimeStrToTime(this=datetime_literal)
6372    if isinstance(value, datetime.date):
6373        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6374        return DateStrToDate(this=date_literal)
6375    if isinstance(value, tuple):
6376        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6377    if isinstance(value, list):
6378        return Array(expressions=[convert(v, copy=copy) for v in value])
6379    if isinstance(value, dict):
6380        return Map(
6381            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6382            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6383        )
6384    raise ValueError(f"Cannot convert {value}")
6385
6386
6387def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6388    """
6389    Replace children of an expression with the result of a lambda fun(child) -> exp.
6390    """
6391    for k, v in expression.args.items():
6392        is_list_arg = type(v) is list
6393
6394        child_nodes = v if is_list_arg else [v]
6395        new_child_nodes = []
6396
6397        for cn in child_nodes:
6398            if isinstance(cn, Expression):
6399                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6400                    new_child_nodes.append(child_node)
6401                    child_node.parent = expression
6402                    child_node.arg_key = k
6403            else:
6404                new_child_nodes.append(cn)
6405
6406        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6407
6408
6409def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6410    """
6411    Return all table names referenced through columns in an expression.
6412
6413    Example:
6414        >>> import sqlglot
6415        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6416        ['a', 'c']
6417
6418    Args:
6419        expression: expression to find table names.
6420        exclude: a table name to exclude
6421
6422    Returns:
6423        A list of unique names.
6424    """
6425    return {
6426        table
6427        for table in (column.table for column in expression.find_all(Column))
6428        if table and table != exclude
6429    }
6430
6431
6432def table_name(table: Table | str, dialect: DialectType = None) -> str:
6433    """Get the full name of a table as a string.
6434
6435    Args:
6436        table: Table expression node or string.
6437        dialect: The dialect to generate the table name for.
6438
6439    Examples:
6440        >>> from sqlglot import exp, parse_one
6441        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6442        'a.b.c'
6443
6444    Returns:
6445        The table name.
6446    """
6447
6448    table = maybe_parse(table, into=Table, dialect=dialect)
6449
6450    if not table:
6451        raise ValueError(f"Cannot parse {table}")
6452
6453    return ".".join(
6454        part.sql(dialect=dialect, identify=True)
6455        if not SAFE_IDENTIFIER_RE.match(part.name)
6456        else part.name
6457        for part in table.parts
6458    )
6459
6460
6461def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6462    """Returns a case normalized table name without quotes.
6463
6464    Args:
6465        table: the table to normalize
6466        dialect: the dialect to use for normalization rules
6467        copy: whether or not to copy the expression.
6468
6469    Examples:
6470        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6471        'A-B.c'
6472    """
6473    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6474
6475    return ".".join(
6476        p.name
6477        for p in normalize_identifiers(
6478            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6479        ).parts
6480    )
6481
6482
6483def replace_tables(
6484    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6485) -> E:
6486    """Replace all tables in expression according to the mapping.
6487
6488    Args:
6489        expression: expression node to be transformed and replaced.
6490        mapping: mapping of table names.
6491        dialect: the dialect of the mapping table
6492        copy: whether or not to copy the expression.
6493
6494    Examples:
6495        >>> from sqlglot import exp, parse_one
6496        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6497        'SELECT * FROM c'
6498
6499    Returns:
6500        The mapped expression.
6501    """
6502
6503    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6504
6505    def _replace_tables(node: Expression) -> Expression:
6506        if isinstance(node, Table):
6507            new_name = mapping.get(normalize_table_name(node, dialect=dialect))
6508
6509            if new_name:
6510                return to_table(
6511                    new_name,
6512                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6513                )
6514        return node
6515
6516    return expression.transform(_replace_tables, copy=copy)
6517
6518
6519def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6520    """Replace placeholders in an expression.
6521
6522    Args:
6523        expression: expression node to be transformed and replaced.
6524        args: positional names that will substitute unnamed placeholders in the given order.
6525        kwargs: keyword arguments that will substitute named placeholders.
6526
6527    Examples:
6528        >>> from sqlglot import exp, parse_one
6529        >>> replace_placeholders(
6530        ...     parse_one("select * from :tbl where ? = ?"),
6531        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6532        ... ).sql()
6533        "SELECT * FROM foo WHERE str_col = 'b'"
6534
6535    Returns:
6536        The mapped expression.
6537    """
6538
6539    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6540        if isinstance(node, Placeholder):
6541            if node.name:
6542                new_name = kwargs.get(node.name)
6543                if new_name:
6544                    return convert(new_name)
6545            else:
6546                try:
6547                    return convert(next(args))
6548                except StopIteration:
6549                    pass
6550        return node
6551
6552    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6553
6554
6555def expand(
6556    expression: Expression,
6557    sources: t.Dict[str, Subqueryable],
6558    dialect: DialectType = None,
6559    copy: bool = True,
6560) -> Expression:
6561    """Transforms an expression by expanding all referenced sources into subqueries.
6562
6563    Examples:
6564        >>> from sqlglot import parse_one
6565        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6566        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6567
6568        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6569        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6570
6571    Args:
6572        expression: The expression to expand.
6573        sources: A dictionary of name to Subqueryables.
6574        dialect: The dialect of the sources dict.
6575        copy: Whether or not to copy the expression during transformation. Defaults to True.
6576
6577    Returns:
6578        The transformed expression.
6579    """
6580    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
6581
6582    def _expand(node: Expression):
6583        if isinstance(node, Table):
6584            name = normalize_table_name(node, dialect=dialect)
6585            source = sources.get(name)
6586            if source:
6587                subquery = source.subquery(node.alias or name)
6588                subquery.comments = [f"source: {name}"]
6589                return subquery.transform(_expand, copy=False)
6590        return node
6591
6592    return expression.transform(_expand, copy=copy)
6593
6594
6595def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6596    """
6597    Returns a Func expression.
6598
6599    Examples:
6600        >>> func("abs", 5).sql()
6601        'ABS(5)'
6602
6603        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6604        'CAST(5 AS DOUBLE)'
6605
6606    Args:
6607        name: the name of the function to build.
6608        args: the args used to instantiate the function of interest.
6609        dialect: the source dialect.
6610        kwargs: the kwargs used to instantiate the function of interest.
6611
6612    Note:
6613        The arguments `args` and `kwargs` are mutually exclusive.
6614
6615    Returns:
6616        An instance of the function of interest, or an anonymous function, if `name` doesn't
6617        correspond to an existing `sqlglot.expressions.Func` class.
6618    """
6619    if args and kwargs:
6620        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6621
6622    from sqlglot.dialects.dialect import Dialect
6623
6624    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6625    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6626
6627    parser = Dialect.get_or_raise(dialect)().parser()
6628    from_args_list = parser.FUNCTIONS.get(name.upper())
6629
6630    if from_args_list:
6631        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6632    else:
6633        kwargs = kwargs or {"expressions": converted}
6634        function = Anonymous(this=name, **kwargs)
6635
6636    for error_message in function.error_messages(converted):
6637        raise ValueError(error_message)
6638
6639    return function
6640
6641
6642def case(
6643    expression: t.Optional[ExpOrStr] = None,
6644    **opts,
6645) -> Case:
6646    """
6647    Initialize a CASE statement.
6648
6649    Example:
6650        case().when("a = 1", "foo").else_("bar")
6651
6652    Args:
6653        expression: Optionally, the input expression (not all dialects support this)
6654        **opts: Extra keyword arguments for parsing `expression`
6655    """
6656    if expression is not None:
6657        this = maybe_parse(expression, **opts)
6658    else:
6659        this = None
6660    return Case(this=this, ifs=[])
6661
6662
6663def true() -> Boolean:
6664    """
6665    Returns a true Boolean expression.
6666    """
6667    return Boolean(this=True)
6668
6669
6670def false() -> Boolean:
6671    """
6672    Returns a false Boolean expression.
6673    """
6674    return Boolean(this=False)
6675
6676
6677def null() -> Null:
6678    """
6679    Returns a Null expression.
6680    """
6681    return Null()
6682
6683
6684# TODO: deprecate this
6685TRUE = Boolean(this=True)
6686FALSE = Boolean(this=False)
6687NULL = Null()